Combining multiple counts into one query - group by?

Up vote 2 down vote favorite share g+ share fb share tw.

I have 3 SELECT statements I'd like to combine into one: SELECT COUNT(A. Id), CONCAT(B. Fname,' ', B.

Lname) AS fullname FROM feedbacks A INNER JOIN users B ON A. Userid = B. Userid WHERE DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname SELECT COUNT(A.

Id), CONCAT(B. Fname,' ', B. Lname) AS fullname FROM feedbacks A INNER JOIN users B ON A.

Userid = B. Userid WHERE status = 'C' AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname` SELECT COUNT(A. Id), CONCAT(B.

Fname,' ', B. Lname) AS fullname FROM feedbacks A INNER JOIN users B ON A. Userid = B.

Userid WHERE caused_change = 1 AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname But combining them always seems to return an error: (Operand should contain 1 column(s)) SELECT (SELECT COUNT(A. Id), CONCAT(B. Fname,' ', B.

Lname) AS fullname FROM feedbacks A INNER JOIN users B ON A. Userid = B. Userid WHERE DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname) AS T1, (SELECT COUNT(A.

Id), CONCAT(B. Fname,' ', B. Lname) AS fullname FROM feedbacks A INNER JOIN users B ON A.

Userid = B. Userid WHERE status = 'C' AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname) AS T2, (SELECT COUNT(A. Id), CONCAT(B.

Fname,' ', B. Lname) AS fullname FROM feedbacks A INNER JOIN users B ON A. Userid = B.

Userid WHERE caused_change = 1 AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname) AS T3 So I tried removing the JOIN and GROUP from the individual statements to make: SELECT CONCAT(B. Fname, ' ', B. Lname) AS fullname, (SELECT COUNT(A.

Id) FROM feedbacks A WHERE DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11') AS T1, (SELECT COUNT(A. Id) FROM feedbacks A WHERE status = 'C' AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11') AS T2, (SELECT COUNT(A. Id) FROM feedbacks A WHERE caused_change = 1 AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11') AS T3 FROM feedbacks INNER JOIN users B ON feedbacks.

Userid = B. Userid GROUP BY fullname But that returns totals for everything vs. breakdown by user (because the counts don't say anything about userid or fullname in the where clause, no doubt). I feel like I'm close but missing something.

Can anyone point me in the correct direction here? I'm just trying to learn what I'm doing wrong. Thank you for your time.

Mysql sql count group-by link|improve this question edited 2011-03-019 at 15:00Scorpi03,0893831 asked 2011-03-019 at 14:51greatcaesarsghost133.

The best way is to use a conditional SUM : SELECT CONCAT(B. Fname, ' ', B. Lname) AS fullname, SUM( CASE WHEN DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' THEN 1 ELSE 0 END ) AS T1, SUM( CASE WHEN status = 'C' AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' THEN 1 ELSE 0 END ) AS T2, SUM( CASE WHEN caused_change = 1 AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' THEN 1 ELSE 0 END ) AS T3 FROM feedbacks INNER JOIN users B ON feedbacks.

Userid = B. Userid GROUP BY fullname.

Thank you, Scorpi0 (and also for the edit). – greatcaesarsghost Jul 12 '11 at 15:11 Thanks! This turned out to be super helpful today.

– Brian Armstrong Sep 23 '11 at 23:28.

I don't know if you can combine those 3 queries into a single unified query. You've got 3 'where clauses' that overlap: first query: ALL records that fall in the date range second query: ALL records that fall in the date range but also have status='C' third query: ALL records that fall in the date range but also have caused_change=1 In logical terms, the first query already contains all the records from the second and third queries, so you'd be double counting the results from #2 and #3. However, if you do want to combine all three into a single result, then do the outer query method: SELECT sum(cnt), fullname FROM ( SELECT COUNT(A.

Id) as cnt, CONCAT(B. Fname,' ', B. Lname) AS fullname FROM feedbacks A INNER JOIN users B ON A.

Userid = B. Userid WHERE DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname UNION ... WHERE status = 'C' AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' UNION ... WHERE caused_change = 1 AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' ) AS innerquery GROUP BY fullname Note the alias on the count() in the first inner query. That's to make the counted field appear as 'cnt' for the outer query.

I was previously grabbing all the results for that date range and using PHP to perform the logic and counts. It was suggested to me that I should let the db do the heavy lifting. Thanks for taking the time to explain.

– greatcaesarsghost Jul 12 '11 at 15:22.

UNION and SUM should do the work. Your code should look like this: select sum(x. Col1), x.

Fullname from (SELECT COUNT(A. Id)as col1, CONCAT(B. Fname,' ', B.

Lname) AS fullname FROM feedbacks A INNER JOIN users B ON A. Userid = B. Userid WHERE DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname UNION SELECT COUNT(A.

Id)as col1, CONCAT(B. Fname,' ', B. Lname) AS fullname FROM feedbacks A INNER JOIN users B ON A.

Userid = B. Userid WHERE status = 'C' AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname UNION SELECT COUNT(A. Id)as col1, CONCAT(B.

Fname,' ', B. Lname) AS fullname FROM feedbacks A INNER JOIN users B ON A. Userid = B.

Userid WHERE caused_change = 1 AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname )x group by x.fullname.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.