CODEWITHSUNDEEP
sqlpostgresqldategroup-bycount
BritishSteel
BritishSteel

Reputation: 181

PostgreSQL - get count of distinct years from date column

I've got a PostgreSQL table with hundreds of rows, each row containing a value in the column date_creation (type date).

The values look as follows:

2010-04-26
2008-08-18
2015-11-11
2010-10-20
2010-03-23
2015-04-08

Now, I'd like to find how often each year occurs.

e.g. in the above example 2010 occurs 3 times, 2015 occurs 2 times, 2008 occurs 1 time

The first part, how to get distinct years from the date, works fine:

SELECT
DISTINCT DATE_PART('year', CAST(date_creation AS DATE)) AS years_available
FROM metadata
WHERE date_creation is not NULL

But I cannot figure out how to also get how often a year occurs.

I’ve found various answers on here that use COUNT, and although they seem logical at first, none of them will return anything useful. I assume it is linked to the query including a DISTINCT statement.

Upvotes: 1

Views: 2214

Answers (2)

GMB
GMB

Reputation: 222432

Use aggregation:

select extract(year from date_creation), count(*) cnt
from metadata
where date_creation is not null
group by 1

Note that there is no need to cast to a date, since you explained that the value has the proper datatype already.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Use aggregation:

SELECT DATE_PART('year', date_creation::date) AS year, count(*)
FROM metadata
WHERE date_creation is not NULL
GROUP BY year;

Of course, if this is a string, string operations are simpler:

SELECT LEFT(date_creation, 4) as year, COUNT(*)
FROM metadata
WHERE date_creation is not NULL
GROUP BY year;

Upvotes: 3

Related Questions