How to Collapse MySQL Rows to Columns Result

Question

There's a query where I want to get the:

• Username of the user attached to the current opportunity record

• Sales Stage associated with each opportunity record

• Dollar amount associated with opportunity record

I want to:

• Take the current IF STATEMENT result, and collapse it

Current Query:

$sql = "SELECT u.user_name as USER,
               if(o.sales_stage='Prospecting', o.amount, '') as PROSPECTING,
               if(o.sales_stage='Needs Analysis', o.amount, '') as NEEDS_ANALYSIS,
               if(o.sales_stage='Closed Won', o.amount, '') as CLOSED_WON
          FROM opportunities o,
               users u
         WHERE o.assigned_user_id = u.id
      GROUP BY u.user_name ";

Current Result:

USER   PROSPECTING  NEEDS_ANALYSIS  CLOSED_WON
---------------------------------------------
chris  10000        0               0
chris  0            15000           0
chris  0            0               10000
sara   5000         0               0
sara   0            0               10000 

What I'd like to do is collapse the results where I only get 1 user, and their respective amounts per SalesStage

USER   PROSPECTING  NEEDS_ANALYSIS  CLOSED_WON
---------------------------------------------
chris  10000        15000           10000
sara   5000         0               10000

Answer 1

you need to add aggregating functions to your $ amounts... ie:

SELECT

u.user_name as USER,

if(o.sales_stage='Prospecting', SUM(o.amount), '') as PROSPECTING,

if(o.sales_stage='Needs Analysis', SUM(o.amount), '') as NEEDS_ANALYSIS,

if(o.sales_stage='Closed Won', SUM(o.amount), '') as CLOSED_WON

FROM

opportunities o,

users u

WHERE

o.assigned_user_id = u.id

GROUP BY

u.user_name

EDIT

Now that I think about it, you'll probably still get separate rows because the sales stages may differ among records for the same user. Have you considered using a routine to do the calculations for each sales stage to collapse your results down to one row per user?

Answer 2

You could wrap the current query in another query, like this:

select subquery.user, 
       sum(subquery.propspecting), 
       sum(subquery.needs_analysis), 
       sum(subquery.closed_won) 
from (*...your query goes here*) subquery 
group by subquery.user

Answer 3

The "collapsing" is generally referred to as "pivoting", because you're converting row into columnar data. Use:

  SELECT u.user_name as USER,
         MAX(CASE WHEN o.sales_stage = 'Prospecting' THEN o.amount END) AS PROSPECTING,
         MAX(CASE WHEN o.sales_stage = 'Needs Analysis' THEN o.amount END) AS NEEDS_ANALYSIS,
         MAX(CASE WHEN o.sales_stage = 'Closed Won' THEN o.amount END) AS CLOSED_WON
    FROM OPPORTUNITIES o
    JOIN USERS u ON u.id = o.assigned_user_id
GROUP BY u.user_name