MySQL If Statement

[emillion82]

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 SELECT IF STATEMENT result, and collapse it based on USER

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 Sales Stage:

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

How might I accomplish this?

Thanks,

- E

[smantscheff]

If the numerical fields are either 0 or the desired value, you may use the maximum of each field. Replace your IF clauses by max() if(o.sales_stage='Prospecting', o.amount, '') as PROSPECTING becomes max(o.amount) as PROSPECTING in your query.
Or, if you have several rows for each category, you might use the sum() instead of the max() function - depends on the semantics of your data.