Search code examples
sqlgoogle-bigquerysummary

Display the total number of each catagory in each column seperately -- SQL


Where is a database like

gender ssc_b
F Central
F Central
F Other
M Central
M Other

I used the count and group by command but it shows:

gender num_gender ssc_b num_ssc_b
F 2 Central 2
F 1 Other 1
M 1 Central 1
M 1 Other 1

I want the display the total number of each catagory in each column seperately, like

gender num_gender ssc_b num_ssc_b
F 3 Central 3
M 2 Other 2

Solution

  • SELECT gender as key, count(*) as value
    FROM <table>
    GROUP BY gender
    UNION ALL
    SELECT ssc_b as key, count(*) as value
    FROM <table>
    GROUP BY ssc_b