Homer_J Homer_J - 4 years ago 115
MySQL Question

MySQL Sum, Count or Join?

I have the following MySQL query:

Country, City,
COUNT(City) As Total,
SUM(completion_status IN ('Started', 'Complete')) AS Total_Resp
FROM trespondent
WHERE completion_status <> 'New'
GROUP BY Country, City
ORDER BY Counntry, City

This brings back what I would expect, which is a list of Cities with the Counts in each, no problem at all

Country City Total Total_Resp
UK London 150 23
UK Leeds 150 83
France Paris 235 99
France Lyon 421 222

What I am trying to do is bring back the Total for the Countries as a whole as well, so UK and France as a whole would look like this:

Country City Total_Country Total_city Total_Resp
UK London 300 150 23
UK Leeds 300 150 83
France Paris 656 235 99
France Lyon 656 421 222

Now, I've tried simply adding to the SQL statement

COUNT(Country) As Total_Country
SUM(Country) As Total_Country

But both bring back the same count as City, I'm now thinking this is not possible without the use of JOIN. Can anyone advise?

Thanks in advance.

Answer Source

You can use a correlated subquery for this:

SELECT t1.Country, t1.City, 
       (SELECT COUNT(t2.Country)
        FROM trespondent AS t2
        WHERE t2.Country = t1.Country) AS Total_Country,
        COUNT(t1.City) As Total_city, 
       SUM(t1.completion_status IN ('Started', 'Complete')) AS Total_Resp
FROM trespondent AS t1
WHERE t1.completion_status <> 'New' 
GROUP BY t1.Country, t1.City 
ORDER BY t1.Country, t1.City
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download