Returning a query with columns as COUNT of a specific category from a field name-CodePudding

Suppose I want to return how many trips were taken by casual & member in a certain route.

https://i.stack.imgur.com/8chn5.png

SELECT
   route,
   COUNT(*) AS count_of_trips
FROM `fresh-ocean-357202.Cyclistic.Cyclistic_clean`
GROUP BY 
  route
ORDER BY 
  count_of_trips DESC
LIMIT 10

CodePudding user response：

Try the following query (requires that your dataset includes a column named like 'User_type' or similar)

SELECT
   route,
   COUNT(*) AS count_of_trips,
   sum(case when User_type = 'Casual_user' then 1 else 0 end) AS Casual_user_count,
   sum(case when User_type = 'Member_user' then 1 else 0 end) AS Member_user_count,
FROM `fresh-ocean-357202.Cyclistic.Cyclistic_clean`
GROUP BY 
  route
ORDER BY 
  count_of_trips DESC
LIMIT 10

Please accept my answer if it covers you.