Group by

[Jonas Nasliunas]

quick question I have a table:

INTRUNK | CALLDROP | DATE
1 | 16 | 2012-03-04
1 | 31 | 2012-03-05
5 | 16 | 2012-03-05
1 | 10 | 2012-03-04
1 | 31 | 2012-03-04


Suppose today is 03-06. I have to count CALLDROP rows (only 16 and 31 is considered) grouped by the INTRUNK in last two days (03-04 and 03-05). FOR EXAMPLE IT HAS TO BE LIKE THIS:


I have to get:

INTRUNK | 16/31 | DATE
1 | 3 | 2012-03-04
5 | 0 | 2012-03-04
1 | 1 | 2012-03-05
5 | 1 | 2012-03-05

my code:


SELECT INTRUNK, SUM(
CASE WHEN CALLDROP = '16'
OR CALLDROP = '31' THEN 1 ELSE 0 END) as '16/31', day(CONNECTTIME) as 'date' from cdr
group by CONNECTTIME, INTRUNK
having day(CONNECTTIME) = EXTRACT(DAY FROM (NOW() - INTERVAL 1 DAY ) )



Thanks in advance.
I am really messed up here. Help!



Edited 1 time(s). Last edit at 03/27/2012 04:29AM by Jonas Nasliunas.