Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Aggregate 'count' with GROUP BY

Status
Not open for further replies.

petschek

Programmer
Joined
Sep 22, 2009
Messages
5
Location
US
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):

Code:
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.

 
SQL Server 2005 and up solution
Code:
;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.
 
Thanks, however, I'm using MySQL, and can't get the right syntax for the OVER() or WITH clauses.
 
YOu should probably post in this forum:
forum436
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top