Postgresql – Count all rows for each DISTINCT

countdistinctpostgresqlquery

How can count all rows of WHERE statement:

SELECT DISTINCT column1 FROM table COUNT WHERE date::text LIKE '%2014%';

My output:

column1
=======
info1
info2
info3

Desired OUTPUT:

column1|number_of_rows
=====================
info1  | 2000
info2  | 1500
info3  | 1000

Can you support me How can improve it?

Thanks in advance.

Best Answer

In this case you can use GROUP BY to get distinct column1 values, and instead of convert the date to text you can use EXTRACT function for this purpose.

select 
    colum1, 
    count(*) as number_of_rows
from 
    your_table
where 
    extract(year from date) = 2014
group by 
    column1;
Related Question