Postgresql Select count of different value of a column

Question

I have a postgresql database with following structure : ID, Date, Name,Classification. The classfication is a foreign key value. It has 3 possible values(lets say C1, C2, C3), Name is a random string.

I want to make a query with counting occurrences of every zone at specific date intervals. I want to make it clear with an example.

Let's say my table data is as follows:

ID      CreateDate       Name     Classification
------+-------------+------------+-------------- 
1       2014-01-01        abc             C1 
2       2012-01-05        def             C2
3       2014-01-01        gef             C2
4       2012-01-01        hjk             C1
5       2014-01-01        lmn             C1
6       2013-06-01        opr             C3
7       2014-01-01        xyz             C1
8       2013-07-01        www             C3

and I want to count the number of classifications in every year. The result set in my example is

2012 1 1 0
2013 0 0 2
2014 3 1 0

The numbers after the date is the number of occurrences of c1, c2, c3 in each year accordingly.

Hope I was being clear.

EDIT: I could manage to count specific classifications with year data with the following query. But i got stuck afterwards.

SELECT COUNT() ,CLASSIFICATION
      ,select count(), classification
             ,extract (year from date_trunc( 'year', CREATEDATE )) as year
         from TABLE_NAME
         group by extract ( year from date_trunc( 'year', CREATEDATE )), CLASSIFICATION
         order by year;

Use a Pivot http://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905 and a sum aggregrate with a group by and a date function to parse out year. If pivot can't be used, then use a case statement for evaluations. — xQbert, Dec 08 '14 at 17:30

xQbert · Accepted Answer · 2018-06-04T18:07:49.633

This process is called conditional aggregation. We simply evaluate specific conditions and when met return 1 else 0 and then use aggregation to sum them. This allows us to pivot the data while obtaining the desired totals.

SELECT extract(year from date t.createdate) as mYear,
sum(case when t.classification = 'C1' then 1 else 0 end) as c1Sum,
sum(case when t.classification = 'C2' then 1 else 0 end) as c2Sum,
sum(case when t.classification = 'C3' then 1 else 0 end) as c3Sum
FROM Table T
GROUP BY mYear

Postgresql Select count of different value of a column

1 Answers1