#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2008
    Location
    Scotland
    Posts
    109
    Rep Power
    7

    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.

    Code:
    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 
    opportunities.`sales_stage`
    But this query is actually frying my brain here so any help on how to achieve this would be greatly appreciated.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2008
    Location
    Scotland
    Posts
    109
    Rep Power
    7
    Sql fiddle of what I have so far.

    http://sqlfiddle.com/#!2/ac28d/1
    Last edited by recci; January 27th, 2014 at 08:57 AM.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2008
    Location
    Scotland
    Posts
    109
    Rep Power
    7
    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.

    http://sqlfiddle.com/#!2/ac28d/13
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2008
    Location
    Scotland
    Posts
    109
    Rep Power
    7
    Answer

    SELECT
    sum(sales_stage="Closed Won")/count(*) * 100 AS won_pct,
    sum(sales_stage="Closed Lost")/count(*) * 100 AS lost_pct
    FROM opportunities

IMN logo majestic logo threadwatch logo seochat tools logo