Count Multiple Types Of Same Column

In my current query: SELECT COUNT(WC.ID) AS 'Regions' FROM WHOLE_FEATURES_PDB_CHAINS AS WC ; I COUNT(WC.ID) AS 'Regions' . However, we have multiple regions with WC.Type can be

Solution 1:

You can use case when statement inside your aggregate function.

Try this .

count(case when WC.type = 1 then 1 end) as region_1, similarly repeat for another column.

Solution 2:

Select
...
...
sum(if WC.ID = 1 then 1else0) as Region1,
sum(if WC.ID = 2 then 1else0) as Region2,
sum(if WC.ID = 3 then 1else0) as Region3,
sum(if WC.ID = 4 then 1else0) as Region4

Might do what you want.

Solution 3:

You can use GROUP BY with COUNT to get the required result, e.g.:

SELECT WC.Type, COUNT(WC.ID) AS"Regions"FROM WHOLE_FEATURES_PDB_CHAINS AS WC 
GROUPBY WC.Type;

Update

If you want the counts as pivoted column for each region then you can write inner SELECT queries, e.g.:

SELECT
 (SELECTCOUNT(ID) FROM WHOLE_FEATURES_PDB_CHAINS WHERE type =1) AS "Region_1",
 (SELECTCOUNT(ID) FROM WHOLE_FEATURES_PDB_CHAINS WHERE type =2) AS "Region_2",
other_column
FROM WHOLE_FEATURES_PDB_CHAINS AS WC
WHERE<somecondition>;