MySQL % question

Question

I'm working with survey data. Essentially, I want to count total responses for a question for a location, then use that total to create what percentage of all responses each particular response is. I then want to group results by location.

An ideal output would be similar to this:

Q1 | State | City | %

yes| MA | bos |10

no | MA | bos |40

m. | MA | bos |50

yes| MA | cam |20

no | MA | cam |20

m. | MA | cam |80

The problem I run into (I believe) is that GROUP BY works before my count statement, so I can't count all the responses. Below is an example of what I have to produce real numbers:

SELECT q1, state, city, COUNT(q1) FROM master GROUP BY state, city, q1

not all questions have responses, so below is my attempt to get %:

SELECT q1, state, city, count(q1)/(count(nullif(q1,0))) as percent FROM master group by state, city, q1

I believe using WITH or OVER(PARTITION BY...) would be a possible avenue, but I can't get either to work. Any help or direction would be much appreciated.

Answer 1

GROUP BY state, city, COUNT(q1)

Answer 2

I think that the query needs to be phrased in two parts. One to get the count per State+City plus another to get the count per State+City+Q1. You then join these two queries together and do the calculation on the combined results. There might be a more elegant solution than this, but something along these lines perhaps might work. Apologies for any typos!

select t1.q1, t1.state, t1.city, ResponseCount, 100.0 * ResponseCount/CityCount as "%"
from
   (select q1, state, city, count(q1) as ResponseCount
    from master
    group by state, city, q1) t1
 join
   (select state, city, count(*) as CityCount
    from master
    group by state, city) t2
 on t2.State = t1.State and t2.City = t1.City

Hope this helps.