英文:
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
基本上,我希望结果按bike_type
和rider_type
进行分组计数。
类似这样的结果:
数据库模式:
英文:
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
Essentially I want the results to show the COUNT grouped by both bike_type AND rider_type.
Something like this:
Database schema:
答案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 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论