mysql percentage question

[petschek]

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, for it's particular location. 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.

 

[SimonSellick]

Hi,

You can use a JOIN to a query that returns the total you are looking for, although it is a little clumsy:

SELECT
  q1
, state
, city
, 100 * (COUNT(q1) / t.tot) percent
FROM master 
  JOIN (SELECT COUNT(q1) tot FROM master) t
GROUP BY 
  state
, city
, q1
ORDER BY
  state
, city
, q1

HTH

 

[petschek]

HTH,

Thanks, but that doesn't give me % per location. It gives me % for the whole. In reference to my initial output in my post, I'm looking for percentages for CAM and for BOS, separately. Thanks!

 

[r937]

SELECT t2.q1
     , t2.state
     , t2.city
     , 100.0 * t2.c / t1.c AS pct
  FROM ( SELECT state
              , city
              , COUNT(*) AS c 
           FROM master 
         GROUP 
             BY state
              , city ) AS t1
INNER
  JOIN ( SELECT q1
              , state
              , city
              , COUNT(*) AS c 
           FROM master 
         GROUP 
             BY q1
              , state
              , city ) AS t2
    ON t2.state = t1.state
   AND t2.city  = t1.city

by the way, neither WITH nor OVER(PARTITION BY...) works in mysql -- yet



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon