Postgresql count different groups in one query

Question

I need to calculate the counts per city,state and country. For example if i have the following data in my table T1:

name      city  state   country
------------------------------
name1      c1     s1      C1
name2      c1     s1      C1
name3      c2     s1      C1
name4      c3     s1      C1
name5      c4     s2      C1
name6      c5     s2      C1
name7      c5     s3      C1
name8      c12    s12     C2

the query should results in:

city    state    country   citycount, statecount, countrycount
-------------------------------------------------------------------
c1       s1        C1        2            4             7
c2       s1        C1        1            4             7
c3       s1        C1        1            4             7
c4       s2        C1        1            2             7
c5       s2        C1        1            2             7
c5       s3        C1        1            1             7
c12      s12       C2        1            1             1

if i do group by and count then i need to write 3 different queries. But i want to do it in one query. please help.

Vivek S. · Accepted Answer · 2015-12-16 07:45:43Z

2

You can use window functions, for example one solution could be this one:

 SELECT DISTINCT city
    ,STATE
    ,country
    ,count(*) OVER (PARTITION BY city,STATE,country) AS citycount
    ,count(*) OVER (PARTITION BY STATE,country) AS statecount
    ,count(*) OVER (PARTITION BY country) AS countrycount
FROM T1
ORDER BY city
    ,STATE
    ,country

Please see a fiddle here.

edited Dec 16, 2015 at 7:45

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

answered Dec 16, 2015 at 7:38

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

Collectives™ on Stack Overflow

Postgresql count different groups in one query

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