CODEWITHSUNDEEP
sqlgoogle-bigquery
Russel Orodio
Russel Orodio

Reputation: 31

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

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

https://i.sstatic.net/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

Upvotes: 1

Views: 122

Answers (1)

Harry
Harry

Reputation: 67

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.

Upvotes: 1

Related Questions