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 |
CodePudding user response：
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
CodePudding user response：
Exactly for what you asked, the answer is
select a.gender, a.s, b.gender, b.s from
(select ROW_NUMBER() OVER() AS num_row, gender, sum(num_gender) s from t group by gender) a
outer join
(select ROW_NUMBER() OVER() AS num_row, ssc_b, sum(num_ssc_b) s from t group by ssc_b) b
on a.num_row=b.num_row
But maybe more logical would be to have the inner queries above as two separate queries.