Aggregate 'count' with GROUP BY

[petschek]

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

An ideal output would be similar to this:

Question1 | State | City | %
yes | MA | bos |10
no | MA | bos |40
maybe | MA | bos |50
yes | MA | cam |20
no | MA | cam |20
maybe | 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 so far (not all questions have responses, so the nullif is an attempt to not count records that don't have responses to that question):

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

Any help or direction would be much appreciated.

 

[markros]

SQL Server 2005 and up solution

;with cte_Counts as (select q1, State, City, count(*) over (partition by State, City) as TotalAnswersByLocation from myTable)

select q1, State, City, count(Q1)/TotalAnswersByLocaition * 100 as Percent from cte_Counts group by State, City, Q1

From the top of my head - not tested.

 

[petschek]

Thanks, however, I'm using MySQL, and can't get the right syntax for the OVER() or WITH clauses.

 

[jbenson001]

YOu should probably post in this forum:
forum436

 

[petschek]

thanks.