Returning the number of distinct count in multiple columns in postgresql

Question

I have a table and need to return distinct data and its count. I'm doing this on 2 different column but haven't met any luck since. Here is an example of what i'm trying to do.

╔════════╦════════════╦══════╦═════════════╗
║  Fog   ║ Count(fog) ║ Snow ║ Count(snow) ║
╠════════╬════════════╬══════╬═════════════╣
║ LA01   ║       1123 ║ NWC  ║         109 ║
║ SIU    ║       3665 ║ SIS  ║          64 ║
║ CHARTN ║        444 ║ PHS  ║         120 ║
╚════════╩════════════╩══════╩═════════════╝

I tried this query but did work

SELECT fog, count(fog), snow, count(snow) FROM message
WHERE arrival_timestamp >= (now() - '48 hour'::INTERVAL) GROUP BY fog, snow

When i tried this, i had the same value for the 2 counts which is incorrect.

What does the data in your table looks like?, and what were your results? — Lamak
– Lamak, Commented Feb 2, 2015 at 19:21
The data are just the number of occurences of each. for example LA01 occurred 1123 times and so on. Don't know if that answered your question — DiD
– DiD, Commented Feb 2, 2015 at 19:25

Gordon Linoff · Accepted Answer · 2015-02-02 19:24:44Z

1

What you want to do requires some work, because the contents of each row are independent. In Postgres, you can do this with a full outer join and row_number():

select f.fog, f.cnt, s.snow, s.cnt
from (select fog, count(*) as cnt,
             row_number() over (order by count(*) desc) as seqnum
      from message
      where arrival_timestamp >= (now() - '48 hour'::INTERVAL)
      group by fog
     ) f full outer join
     (select snow, count(*) as cnt,
             row_number() over (order by count(*) desc) as seqnum
      from message
      where arrival_timestamp >= (now() - '48 hour'::INTERVAL)
      group by snow
     ) s
     on f.seqnum = s.seqnum;

This is ordered by the highest count first -- which is not the case in your sample results.

answered Feb 2, 2015 at 19:24

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

DiD Over a year ago

Waoh, works like magic. Here is my next big problem. I want to add 2 more columns that compares count within 48hours and count within 30days. This query is the count for 48 hours, how do i modify the query to add counts for 30days. I hope it makes sense

Gordon Linoff Over a year ago

@DiD . . . You should ask another question, providing sample data and desired results.

Collectives™ on Stack Overflow

Returning the number of distinct count in multiple columns in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related