CODEWITHSUNDEEP
sqlcountgroup-by
Wick
Wick

Reputation: 51

How can we use multiple count and multiple group by in SQL at the same time?

So i have this table 

             `gender  country  age  name  ID
              male    VietNam  20   Q     890 
              female  China    30   K     209
              female  Japan    25   O     302
              female  VietNam  20   L     203
              male    China    20   E     504`

i want to make a query which can list the number of people who has the same gender,same country and same age in individual by count and group by like this

Select gender,COUNT(*) from Employee Group BY gender Select country,COUNT(*) from Employee Group BY country Select age,COUNT(*) from Employee Group BY age

Is there any way to make it in just one select ? Thanks in advance and sorry for my bad english

Upvotes: 2

Views: 624

Answers (2)

xQbert
xQbert

Reputation: 35333

If your RDBMS supports it... SQL Server, Oracle and others do.... not sure what you're using.

Group by Grouping sets

Select count(*), gender, country, age 
from employee 
group by grouping sets ((gender), (country), (age ))

and depending on the type of output you're looking for... this will result in 4 columns a count, gender, country, and age columns where 2 of the 4 columns will always be null.

it will result in a similar output to JNevill's except no groupbyType, instead there's a separate column for each value in the group by

Upvotes: 2

JNevill
JNevill

Reputation: 50064

You could UNION to get these results in separate records of your result set:

 Select 'Gender' as GroupByType, gender as GroupByValue ,COUNT(*) from Employee Group BY gender
 UNION ALL
 Select 'Country', country,COUNT(*) from Employee Group BY country
 UNION ALL
 Select 'Age', age,COUNT(*) from Employee Group BY age

Upvotes: 2

Related Questions