Working out percentages in a Mysql query for reporting tool zuckerreports

Although not strictly Sugarcrm related I had requirement the needed me to work out the percentage of opportunities which had the sale_stage field set to ‘Closed Lost’ and ‘Closed Won’. This would be displayed as part of sugar via Zuckerreports, effectively showing a report with the percentage of all Opportunities that were Won and Lost.

As it turned out this was easy:

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

The second part of this report was much more complicated as follows:

For our purposes the opportunities table contains the following fields: id and sales_stage. The opportunities_cstm table contains the fields id_c and sales_stage_before_closed_c. 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 SugarCRM instance 1 to 10 represents percentage bands from 0-9% to 90-99% and closed lost is 0% and closed won is 100%.

sales_stage_before_closed_c is the percentage band that the opportunity was at before it was closed.

So in my actual query I needed to display a percentage for each sales_stage on how many opportunities reached this stage and resulted in a won opportunity and how many reached this stage and resulted in a lost opportunity.

After much brain strain and help from a college:

SELECT opportunities_c_top.sales_stage_before_closed_c AS 'Sales Stage',
FORMAT(
( SELECT COUNT(*)
 FROM `opportunities_cstm` opportunities_cstm join 
 `opportunities` opportunities
 on opportunities_cstm.id_c = opportunities.id WHERE opportunities.`sales_stage` = 'Closed Won' AND opportunities_cstm.sales_stage_before_closed_c = opportunities_c_top.sales_stage_before_closed_c )* 100 / COUNT(*), 2) AS 'Closed Won',
FORMAT(
 ( SELECT COUNT(*)
 FROM `opportunities_cstm` opportunities_cstm join 
 `opportunities` opportunities
 on opportunities_cstm.id_c = opportunities.id WHERE opportunities.`sales_stage` = 'Closed Lost' AND opportunities_cstm.sales_stage_before_closed_c = opportunities_c_top.sales_stage_before_closed_c )* 100 / COUNT(*), 2) AS 'Closed Lost'

FROM `opportunities_cstm` opportunities_c_top join 
 `opportunities` opportunities_top
 on opportunities_top.id = opportunities_c_top.id_c
WHERE (opportunities_top.`sales_stage` = 'Closed Won' OR opportunities_top.`sales_stage` = 'Closed Lost') 
GROUP BY opportunities_c_top.sales_stage_before_closed_c

An sql fiddle showing a working example: Fiddle

Andy
About

Software Developer from Falkirk, Central Scotland.

Posted in General Programming

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>