CODEWITHSUNDEEP
mysqlsqlpercentage
AxW
AxW

Reputation: 662

Calculate percentage by group based on values in one column

Apologies for the basic question, I could not find the answer after searching.

A simple table with 2 columns car_type and event. Each time a customer inquires about a car, a record is put into the table and event = "inquire". If that car type is bought, a record of event = "bought" is put in.

How would I calculate which car type is the most successful, based off # bought / # inquired? Grouped by car type of course.

I've tried 

select car_type, 
      ((select count(*) from TABLE where event = "bought" ) / 
       (select count(*) from alerts where event = "inquire") * 100.0) as percentage
from TABLE 
group by car_type;

but this hasn't worked.

Thank you in advance!

Upvotes: 0

Views: 193

Answers (2)

Nick
Nick

Reputation: 147166

You can sum the different event types for each car (most easily in a subquery) and then divide the results to get the percentage, ordering by that value descending and taking only the top value:

SELECT car_type, 100.0 * bought / inquiries AS percentage
FROM (
    SELECT car_type,
           SUM(event = 'bought') AS bought,
           SUM(event = 'inquire') AS inquiries
    FROM alerts
    GROUP BY car_type
) c
ORDER BY percentage DESC
LIMIT 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You could use conditional aggregation:

select car,
       (sum( event = 'bought') /
        sum( event = 'inquire' )
       ) as bought_inquire_ratio
from t
group by car;

If you simply want the bought ratio overall you can use:

select car,
       avg( event = 'bought' )
from t
group by car;

Upvotes: 1

Related Questions