PostgreSQL - aggregate counts

Question

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.

Oto Shavadze · Accepted Answer · 2017-08-04 09:21:32Z

1

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

answered Aug 4, 2017 at 9:21

Oto Shavadze

43.3k56 gold badges169 silver badges248 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - aggregate counts

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related