getting sum of count() in groupby

Question

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?

Martin Smith · Accepted Answer · 2011-04-08 22:23:09Z

6

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.

edited Apr 8, 2011 at 22:23

answered Apr 8, 2011 at 22:14

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MalTec Over a year ago

thnx a lot. luking at my previous two questions.. And their answers.. seems i need to work on sql queries..

Martin Smith Over a year ago

You also seem to be missing a JOIN condition I think. How are the 2 tables supposed to be related?

MalTec Over a year ago

oh yes.. while editing i guess i removed the wrong portion.. thnx for drwng my attention

Collectives™ on Stack Overflow

getting sum of count() in groupby

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related