Reputation: 151
I have a query that grabs the counts of complaints for a city.
query = '''
select ComplaintType as complaint_type, City as city_name,
count(ComplaintType) complaint_count
from data
where city in ({})
group by city_name, complaint_type
order by city_name
'''.format(strs_to_args(TOP_CITIES))
ComplaintType City_name .Complain_
Now, I want to create a column to compute the complaints of type t that occurred in city. It would be something like count(ComplaintType)/sum(count(ComplaintType) in city)
What's the best syntax to accomplish this?
query = '''
select ComplaintType as complaint_type, City as city_name,
count(ComplaintType)/sum(count(ComplaintType) as complaint_freq
Upvotes: 0
Views: 1257
Reputation: 1271171
Well, one method is to summarize in a subquery and join the results in:
query = '''
select d.ComplaintType as complaint_type, d.City as city_name,
count(*) as complaint_count,
count(*) * 1.0 / max(cc.cnt) as ratio
from data d cross join
(select d.city, count(*) as cnt
from data d
group by d.city
) cc
on d.city = cc.city
where d.city in ({})
group by d.city, d.complaint_type
order by d.city
'''.format(strs_to_args(TOP_CITIES))
Upvotes: 1