This my Table :
| name | status | type |
|---|---|---|
| one | active | paid |
| five | non active | free |
| ten | active | paid |
| one | active | paid |
| four | non active | free |
| three | active | paid |
| two | non active | free |
the output i want is :
| status | status count | type | type count |
|---|---|---|---|
| active | 4 | free | 3 |
| non active | 3 | paid | 4 |
can I achieve this output Within a single Query ?.
I can get output by quering two times and store it seperate object in Java :
for status count:
select status , count(status) from table group by status;
for type count :
select type , count(type) from table group by type;
i have to query two times ?
CodePudding user response：
Try below query containing JOIN of two SUBQUERIES
select * from
(select status , count(status)status_count from table group by status)table1
left join
(select type , count(type)type_count from table group by type)table2
on table1.status=table2.type;
CodePudding user response：
select status, case when status='active' then count(status) when status ='non-active' then count(status) end as status_count, type, case when type='free' then count(type) when type ='paid' then count(type) end as type_count from tab group by status,type