SUM and Count in one SQL Query

Question

I have this kind of data

time                                               Members
-------------------------------------------------- -----------
Jun 23 2016                                           1
Jun 23 2016                                           1
Jun 23 2016                                           2
Jun 29 2016                                           6
Jul 11 2016                                           3
Jul 11 2016                                           1
Jul 13 2016                                           1

I obtained this data using this sql query

SELECT CONVERT (VARCHAR(12), a.registered_time), COUNT(b.member_id) AS Members
FROM b
Inner JOIN a ON b.mirror_id = a.mirror_id 
GROUP BY 
(a.registered_time) order by a.registered_time

I want to get the sum of total numbers if they are of the same date for exampple the date of June 23 2016 will have total members of 4 and so on. Is it possible to have SUM() FUnction on Count()? How can I do this?

Count of menberid itself give you the 4 for that date, no need of sum again there, and convert registered date also to date in group by — Rams
– Rams, Commented Oct 11, 2017 at 2:33

Gordon Linoff · Accepted Answer · 2017-10-11 02:50:43Z

0

Convert the value to a date and include that in both the select and group by:

SELECT CONVERT(date, a.registered_time) as dte, COUNT(b.member_id) AS Members
FROM b JOIN
     a
     ON b.mirror_id = a.mirror_id 
GROUP BY CONVERT(date, a.registered_time)
ORDER BY CONVERT(date, a.registered_time);

edited Oct 11, 2017 at 2:50

answered Oct 11, 2017 at 2:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

bleyk Over a year ago

Returns me an error that registered_time is invalid in ORDER BY because it is not contained in either an aggregate function or the GROUP BY clause.

Gordon Linoff Over a year ago

@bleykFaust . . . That should be the expression or dte.

bleyk Over a year ago

How about I I only want to get the year for the time?

Gordon Linoff Over a year ago

@bleykFaust . . . You would use year() or datepart() or datename().

Collectives™ on Stack Overflow

SUM and Count in one SQL Query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related