SELECT calldate, from_unixtime(FLOOR(UNIX_TIMESTAMP(calldate)/(60*60))*(60*60)) GroupTime
, COUNT(*) as CntOut FROM asterisk.cdr
WHERE accountcode = '10102-131' AND DATE (calldate) = DATE (NOW()) AND calltype = 'outgoing'
GROUP BY GroupTime;
I have this query which groups a count for every hour of date for outgoing calls. Is there any way to get also the count of incoming as well in the same query?
Try this:
SELECT
Calldate
from_unixtime(FLOOR(UNIX_TIMESTAMP(calldate)/(60*60))*(60*60)) GroupTime,
SUM(calltype='incoming') as CntIn,
SUM(calltype='outgoing') as CntOut
FROM asterisk.cdr
WHERE accountcode = '10102-131'
AND DATE (calldate) = DATE (NOW())
AND calltype IN ('incoming', 'outgoing' )
GROUP BY GroupTime;
Assuming that "outgoing" and "incoming" are descriptors on the call:
SELECT date(calldate), hour(calldate) as callhour,
count(*) as Cnt,
sum(calltype = 'outgoing') as CntOut,
sum(calltype = 'incoming') as CntIn
FROM asterisk.cdr
WHERE accountcode = '10102-131' AND
(calldate >= curdate() and calldate < date_sub(curdate(), interval 1 day)
) AND
calltype IN ('outgoing' , 'incoming')
GROUP BY date(calldate), hour(calldate);
Notes:
date() on calldate can prevent the use of an index. So, I replaced that logic with inequalities.If it is ok ot group by calltype, try:
SELECT
calltype
, calldate
, from_unixtime(FLOOR(UNIX_TIMESTAMP(calldate)/(60*60))*(60*60)) GroupTime
,case when calltype='outgoing' then COUNT(*) else 0 end as CntOut
,case when calltype='ingoing' then COUNT(*) else 0 end as CntIn
FROM asterisk.cdr
WHERE
accountcode = '10102-131'
AND DATE (calldate) = DATE (NOW())
AND (calltype = 'outgoing' or calltype='ingoing')--if calltype in only 'ingoing' or 'outgoing', you can omit this line
GROUP BY calltype, calldate;
But if not, the query would be a bit complicated:
SELECT
t.calldate
, from_unixtime(FLOOR(UNIX_TIMESTAMP(t.calldate)/(60*60))*(60*60)) GroupTime
, t_out.cnt as CntOut
, t_in.cnt as CntIn
FROM
asterisk.cdr as t
left join (select calldate,count(*) cnt from asterisk.cdr where calltype='outgoing' group by calldate) as t_out
on t.calldate=t_out.calldate
left join (select calldate,count(*) cnt from asterisk.cdr where calltype='ingoing' group by calldate) as t_in
on t.calldate=t_in.calldate
WHERE
t.accountcode = '10102-131'
AND DATE (t.calldate) = DATE (NOW())
GROUP BY t.calldate;