在GoogleSQL中使用COUNT函数对两个变量进行计数。

huangapple go评论67阅读模式
英文:

Using COUNT on SQL in GoogleSQL for two variables

问题

我正在尝试对rider_type中的两个变量和bike_type中的三个变量进行计数。

我只能以这种方式使其工作:

SELECT bike_type,
COUNT(rider_type) AS num_of_rides
FROM `cyclistic-395223.trips.12_month_tripdata_clean`
WHERE rider_type = 'casual'
GROUP BY bike_type
UNION ALL
SELECT bike_type,
COUNT(rider_type) AS num_of_rides
FROM `cyclistic-395223.trips.12_month_tripdata_clean`
WHERE rider_type = 'member'
GROUP BY bike_type

在GoogleSQL中使用COUNT函数对两个变量进行计数。

基本上,我希望结果按bike_typerider_type进行分组计数。
类似这样的结果:

在GoogleSQL中使用COUNT函数对两个变量进行计数。

数据库模式:

在GoogleSQL中使用COUNT函数对两个变量进行计数。

英文:

I am trying to do COUNT for two variables in rider_type and three variables in bike_type.

I am only able to get it to work like this:

SELECT bike_type,
COUNT(rider_type) AS num_of_rides
FROM `cyclistic-395223.trips.12_month_tripdata_clean`
WHERE rider_type = 'casual'
GROUP BY bike_type
UNION ALL
SELECT bike_type,
COUNT(rider_type) AS num_of_rides
FROM `cyclistic-395223.trips.12_month_tripdata_clean`
WHERE rider_type = 'member'
GROUP BY bike_type

在GoogleSQL中使用COUNT函数对两个变量进行计数。

Essentially I want the results to show the COUNT grouped by both bike_type AND rider_type.
Something like this:

在GoogleSQL中使用COUNT函数对两个变量进行计数。

Database schema:

在GoogleSQL中使用COUNT函数对两个变量进行计数。

答案1

得分: 0

你可以按多个字段进行分组。

SELECT bike_type, rider_type, COUNT(*) AS num_of_rides
FROM cyclistic-395223.trips.12_month_tripdata_clean
WHERE rider_type IN ('casual', 'member')
GROUP BY bike_type, rider_type

示例:

WITH _table AS (
    SELECT 'casual' AS rider_type, 'docked_bike' AS bike_type UNION ALL
    SELECT 'casual', 'classic_bike' UNION ALL
    SELECT 'casual', 'electric_bike' UNION ALL
    SELECT 'member', 'docked_bike' UNION ALL
    SELECT 'member', 'electric_bike' UNION ALL
    SELECT 'member', 'classic_bike' UNION ALL
    SELECT 'member', 'docked_bike'
  )
SELECT bike_type, rider_type, COUNT(*) AS num_of_rides
FROM _table
WHERE rider_type IN ('casual', 'member')
GROUP BY bike_type, rider_type
bike_type rider_type num_of_rides
docked_bike casual 1
classic_bike casual 1
electric_bike casual 1
docked_bike member 2
electric_bike member 1
classic_bike member 1
英文:

You can group by more than one field.

SELECT bike_type, rider_type, COUNT(*) AS num_of_rides
FROM cyclistic-395223.trips.12_month_tripdata_clean
WHERE rider_type IN ('casual', 'member')
GROUP BY bike_type, rider_type

Example:

WITH _table AS (
    SELECT 'casual' AS rider_type, 'docked_bike' AS bike_type UNION ALL
    SELECT 'casual', 'classic_bike'UNION ALL
    SELECT 'casual', 'electric_bike' UNION ALL
    SELECT 'member', 'docked_bike' UNION ALL
    SELECT 'member', 'electric_bike' UNION ALL
    SELECT 'member', 'classic_bike' UNION ALL
    SELECT 'member', 'docked_bike'
  )
SELECT bike_type, rider_type, COUNT(*) AS num_of_rides
FROM _table
WHERE rider_type IN ('casual', 'member')
GROUP BY bike_type, rider_type
bike_type rider_type num_of_rides
docked_bike casual 1
classic_bike casual 1
electric_bike casual 1
docked_bike member 2
electric_bike member 1
classic_bike member 1

huangapple
  • 本文由 发表于 2023年8月9日 05:38:45
  • 转载请务必保留本文链接:https://go.coder-hub.com/76863356.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定