CODEWITHSUNDEEP
sqlpostgresqlaggregate
Ian Hudson
Ian Hudson

Reputation: 21

PostgreSQL - aggregate counts

I have the following PostgreSQL table for which I am trying to get some aggregate totals for. 

number | firstused  | monthtotal 
--------+------------+------------
264 | 2017-11-02 |          1
269 | 2017-11-02 |          1
262 | 2017-01-02 |          3
270 | 2017-11-02 |          2
268 | 2017-10-02 |          1
265 | 2017-04-02 |          1
267 | 2017-11-02 |          1
263 | 2017-01-02 |          3
266 | 2017-04-02 |          3

I would like help with a query that could return a result set that looks like the following. Each row has the total of the how many monthtotals had an equal value for a year/ month. The last two rows of the example show that for 2017-11 it had 3 counts of monthtotal 1 and 1 count of monthtotal 2

firstused | monthtotal | total
----------+------------+------
2017-01-02| 3          | 2
2017-04-02| 1          | 1
2017-04-02| 3          | 1
2017-10-02| 1          | 1
2017-11-02| 1          | 3
2017-11-02| 2          | 1

Could a query that generates the above be also be expressed as a Hibernate criteria.

Upvotes: 0

Views: 33

Answers (1)

Oto Shavadze
Oto Shavadze

Reputation: 42753

select firstused, monthtotal, count(*) as total
from t
group by firstused, monthtotal

Upvotes: 1

Related Questions