-1

I just need an help on making an query work.

I wanted To get the records for an all users whose total cdr seconds is >= 2400 (40 minutes)

SELECT SUM(TIME_TO_SEC(cdr.duration)) AS secsUsed 
FROM cdr 
WHERE SUM(TIME_TO_SEC(cdr.duration))>=2400 
GROUP BY ownerpin 
ORDER BY secsUsed DESC

i found that aggregate function usage like above will not work!

is there any way that i could get the logic work !!

David
  • 19,577
  • 28
  • 108
  • 128
Ramesh Lingappa
  • 2,448
  • 20
  • 33

1 Answers1

1

Move the condition from WHERE to HAVING clause:

select ownerpin,
       SUM(TIME_TO_SEC(cdr.duration)) as secsUsed 
from cdr 
group by ownerpin 
HAVING SUM(TIME_TO_SEC(cdr.duration))>=2400 
order by secsUsed desc ;
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • Thanks , This query works!!! also i have another issue, check the below query select SUM(TIME_TO_SEC(cdr.duration)) as secsUsed ,cdr.ownerpin, user.userid, DATEDIFF(now(),user.dateadded) as daydiff from cdr inner join (select ownerpin,userid,dateadded,is_active,plan_type from user where user.is_active =true and user.plan_type like '%trial%' ) as user on cdr.ownerpin =user.ownerpin where DATEDIFF(now(),dateadded) >=14 or SUM(TIME_TO_SEC(cdr.duration)) >=2400 group by cdr.ownerpin order by secsUsed desc; – Ramesh Lingappa Feb 21 '13 at 08:19
  • now i wanted to perform two condition that is either the user's has passed 14 days or his/her cdr minutes exceeds 40 minutes can u help Error 1111: Invalid use of group function this also throwing – Ramesh Lingappa Feb 21 '13 at 08:21
  • Sorry for not formatting the second query !!! Any Help!! – Ramesh Lingappa Feb 21 '13 at 08:25
  • i Justed wanted to make the following condition work where DATEDIFF(now(),dateadded) >=14 or SUM(TIME_TO_SEC(cdr.duration)) >=2400 – Ramesh Lingappa Feb 21 '13 at 08:27