MySQL How to return a COUNT overall and also a COUNT using GROUP BY in one query

Question

I have the following query:

SELECT location, COUNT( location ) AS Total, (

SELECT location, COUNT( location ) AS Responses
FROM  `trespondent` 
WHERE completion_status IN ('Started',  'Complete')
GROUP BY location
)

FROM  `trespondent` 
GROUP BY location

This brings back an error:

Operand should contain 1 column(s)

Which is entirely correct as there will be more than one row.

What I am trying to achieve, within a single query, is to bring back the Total number for each option in 'Location' and then also the the number that have 'Started or 'Completed' for each option in 'Location'.

Any suggestions if this is possible within a single query and if so, any pointers welcomed.

Rahul · Accepted Answer · 2014-10-19 12:58:10Z

1

You can change your query like below using UNION clause to use both version of query and get the overall result

SELECT location, COUNT( location ) AS Total
FROM  `trespondent`
GROUP BY location

UNION ALL

SELECT location, COUNT( location ) AS Responses
FROM  `trespondent` 
WHERE completion_status IN ('Started',  'Complete')
GROUP BY location

Or probably using JOIN to join both query result like

SELECT t1.location as T1location, 
COUNT( t1.location ) AS Total,
tab.Responses,
tab.location as tablocation
FROM  `trespondent` t1 
JOIN
(
SELECT location, COUNT( location ) AS Responses
FROM  `trespondent` 
WHERE completion_status IN ('Started',  'Complete')
GROUP BY location
) tab
ON t1.location = tab.location
GROUP BY t1.location

answered Oct 19, 2014 at 12:58

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

1 Comment

Homer_J Over a year ago

The option using 'JOIN' is exactly what I was looking for. Worth noting that the 'UNION' also provide the same information but laid out in a different way (vertically). Thanks @Rahul.

slaakso · Accepted Answer · 2014-10-19 13:30:13Z

1

You can use conditional sum for the second calculation:

SELECT 
  location, 
  COUNT(*) AS Total, 
  SUM( IF(completion_status IN ('Started',  'Complete'), 1, 0)) as 'Responses'
FROM  trespondent 
GROUP BY location

answered Oct 19, 2014 at 13:30

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2014-10-19 19:51:07Z

0

How about this?

SELECT location, COUNT( location ) AS Total,
       (SELECT COUNT( location ) AS Responses
        FROM  `trespondent` 
        WHERE completion_status IN ('Started',  'Complete')
       )
FROM  `trespondent` 
GROUP BY location;

That is, the subquery for the total doesn't need a group by.

EDIT:

If you want a count, for each location, of the two statuses, then just use conditional aggregation:

SELECT location, COUNT(*) AS Total, SUM(completion_status IN ('Started',  'Complete'))
FROM  `trespondent` 
GROUP BY location;

This seems like the simplest solution.

edited Oct 19, 2014 at 19:51

answered Oct 19, 2014 at 12:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Homer_J Over a year ago

@Gordon_Linoff - thanks for this, unfortunately it brings back the 'same' total for every option in Location which isn't what I was looking for. Thanks all the same.

Gordon Linoff Over a year ago

@Homer_J . . . your question was unclear (to me) on what to do. The simplest solution would be the version I just added.

Collectives™ on Stack Overflow

MySQL How to return a COUNT overall and also a COUNT using GROUP BY in one query

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related