January 27th, 2014, 05:50 AM
Mysql sub-query with percentage breakdown (difficult query)
Ok I have two related tables one which contains the main fields called 'opportunities' and one which contains addition fields called 'opportunities_cstm'. For our purposes the opportunities table contains the following fields: id and sales_stage. The opportunities_cstm table contains the fields id_c and percentage_before_closed. id_c is what relates the two tables.
sales_stage contains the values from 1 to 10 and also either 'Closed Lost' or 'Closed Won'. In the actual application 1 to 10 represent percentage bands from 0-9% to 90-99% and closed lost is 0% and closed won is 100%.
percentage_before_closed stores the percentage band that it was at before it was closed so it can contain any number from 0 to 99.
So in my actual query I need display a percentage for each sales_stage on how many opportunities reached this stage and resulted in a won opportunity and how many reach this stage and resulted in a lost.
So far I only have the following sql query but I realise I have to fit two sub-queries in somewhere to get the closed won and closed lost percentages.
But this query is actually frying my brain here so any help on how to achieve this would be greatly appreciated.
SELECT opportunities.`sales_stage` ,
opportunities_cstm.`percent_before_closed_c` FROM `opportunities`
opportunities, `opportunities_cstm` opportunities_cstm WHERE
opportunities.`sales_stage` != 'Closed Lost' AND
opportunities.`sales_stage` != 'Closed Won' GROUP BY
January 27th, 2014, 06:59 AM
Sql fiddle of what I have so far.
Last edited by recci; January 27th, 2014 at 08:57 AM.
January 27th, 2014, 02:54 PM
Ok how about I just get the percentage of rows where sales_stage is "Closed Won" and also get percentage of rows where sales_stage is "Closed Lost" And basically just display the two headings Closed won and Closed Lost.
January 27th, 2014, 03:22 PM
sum(sales_stage="Closed Won")/count(*) * 100 AS won_pct,
sum(sales_stage="Closed Lost")/count(*) * 100 AS lost_pct