CODEWITHSUNDEEP
pythonmysqlsql
GenXeral
GenXeral

Reputation: 151

Calculating percentage/fraction from sum in python sql query

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions