CODEWITHSUNDEEP
mysqlsql
MalTec
MalTec

Reputation: 1370

getting sum of count() in groupby

Dont know if i am breaking comuntiy guidelines by posting a continuation question as new question. If so. I am sorry!!

Now, using, 

SELECT count(alertid) as cnt,date(alertdate) as alertDate 
FROM alertmaster a,subscriptionmaster s 
WHERE alertDate BETWEEN DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND CURDATE() 
GROUP BY date(alertDate),s.subId 
ORDER BY a.alertDate DESC;

produces:
13, '2011-04-08'
13, '2011-04-08'
13, '2011-04-08'
14, '2011-04-07'
13, '2011-04-07'

Where I want is:

39, '2011-04-08'
27, '2011-04-07'

How to achieve this?

Upvotes: 1

Views: 264

Answers (1)

Martin Smith
Martin Smith

Reputation: 453067

The reason you are getting more than one row per date is because you have GROUP BY date(alertDate),s.subId. Just change your GROUP BY to 

GROUP BY date(alertDate)

If you don't actually want separate groups for each s.subId,date combination.

Also the code you posted is missing a JOIN condition. This is one reason why using the explicit (ANSI 92) JOIN syntax is preferred.

Upvotes: 6

Related Questions