How to organize row wise results in column wise results SQL-CodePudding

I will spare you of the details about my journey, but as you can see I'm really new in SQL, and I'm trying to get my head around how to make queries etc. My question is regarding the following snippet

select 
(select count ("code_id")
from example_table  where "code_id" = 3), 
(select 3 as ecoregion),
(select count ("code_id")
from example_table  where "code_id" = 4), 
(select 4 as ecoregion),
(select count ("code_id")
from example_table  where "code_id" = 5), 
(select 5 as ecoregion)

The problem is that generates multiple columns with the same name (one for count and one for the number of elements aliased "ecoregion"). My question is: How can I modify the code snippet so I will end up having only two columns, one called count and the other ecoregion and all the results of the queries under them?

CodePudding user response：

select code_id as ecoregion, count(*) as "count"
from T
where code_id in (3, 4, 5)
group by code_id

CodePudding user response：

You can do this by using case when like in this example:

select case when code_id = 3 then
            count (code_id) 
       when code_id = 4 then
            count (code_id) 
       when code_id = 5 then
            count (code_id)
       end "count",
       case when code_id = 3 then
            '3'
       when code_id = 4 then
            '4'
       when code_id = 5 then
            '5'
       end "ecoregion"
from example_table
group by code_id

Here you can check the demo

CodePudding user response：

You can also use UNION to attach rows of 2/more SELECT statements.

select count ("code_id"), 3 as ecoregion
from example_table  where "code_id" = 3

UNION

select count ("code_id"), 4 as ecoregion
from example_table  where "code_id" = 4

UNION

select count ("code_id"), 5 as ecoregion
from example_table  where "code_id" = 5

There is also UNION ALL, which allows duplicate rows.

Considering your case, I'd suggest you go with the UNION as it is fairly an easier concept than the CASE WHEN.
You'll get to the CASE WHEN too.