current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.5 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

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.

share|improve this question
    
What does the data in your table looks like?, and what were your results? – Lamak Feb 2 '15 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 Feb 2 '15 at 19:25
up vote 0 down vote accepted

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.

share|improve this answer
    
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 – DiD Feb 2 '15 at 19:45
    
@DiD . . . You should ask another question, providing sample data and desired results. – Gordon Linoff Feb 2 '15 at 20:07
    
Ok, thanks. Will do that and provide sample data – DiD Feb 2 '15 at 20:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.