How do I Put Several Select Statements into Different Columns with Group BY

I have this table, for simplicity I included only 1 customer name, it has many

+--------------+---------------------+---------------+
| customername | customercontactname | statename |
+--------------+---------------------+---------------+
| IKEA | Sam | Won |
| IKEA | Sam | Won |
| IKEA | Sam | Won |
| IKEA | Sara | Won |
| IKEA | Sara | Won |
| IKEA | Sara | Won |
| IKEA | Sara | Won |
| IKEA | Amelia | Lost |
| IKEA | Maya | Won |
| IKEA | Maya | Won |
+--------------+---------------------+---------------+


I want this output

+--------------+---------------------+---------+----------+
| customername | customercontactname | WonOpps | LostOpps |
+--------------+---------------------+---------+----------+
| IKEA | Sam | 3 | NULL |
| IKEA | Sara | 4 | NULL |
| IKEA | Maya | 2 | NULL |
| IKEA | Amelia | NULL | 1 |
+--------------+---------------------+---------+----------+


Trial(the result is fine for the first 3 rows, however Amelia is not showing in my final output)

SELECT t1.customername,
t1.customercontactname,
t1.wonopps,
t2.lostopps
FROM (SELECT customername,
customercontactname,
Count(*) AS WonOpps
FROM mytable
WHERE statename = 'won'
GROUP BY customername,
customercontactname) t1
LEFT JOIN (SELECT customername,
customercontactname,
Count(*) AS LostOpps
FROM mytable
WHERE statename = 'lost'
GROUP BY customername,
customercontactname) t2
ON t1.customername = t2.customername
AND t1.customercontactname = t2.customercontactname


4 Answers
4

Use conditional aggregation:

select customername, customercontactname, count(case when statename='Won' then 1 end ) WonOpps,
count(case when statename='Lost' then 1 end ) WonLost
from tablename
group by customername, customercontactname


use sum

sum

with t1 as
(
select customername,
customercontactname,
sum(case when statename='won' then 1 else 0 end ) as WonOpps,
sum(case when statename='loss' then 1 else 0 end ) as LostOpps
from t
group by customername, customercontactname
) select customername,customercontactname, case when WonOpps>1 then WonOpps else null end as WonOpps,
case when LostOpps>1 then LostOpps else LostOpps end as LostOpps from t1


You can use SUM combined with CASE WHEN:

SUM

CASE WHEN

SELECT customername,
customercontactname,
SUM(CASE WHEN statename = 'Won' THEN 1 END) AS WonOpps,
SUM(CASE WHEN statename = 'Lost' THEN 1 END) AS WonLost
FROM mytable
GROUP BY customername, customercontactname


SUM returns NULL if no records match because we do not specify ELSE-values.

SUM

NULL

ELSE

If you prefer to see 0 instead, then you can add ELSE 0, or you can use COUNT instead of SUM.

0

ELSE 0

COUNT

SUM

The reason your query is not giving row for 'Amelia' is because you are using LEFT JOIN and because Amelia has not any 'won' value in statename column so its null on left side of join. Just to explain concept well, I am giving a simple solution.

See following query:

select a.customername, a.customercontactname, a.WonOpps, b.LostOpps from
(select customername,customercontactname, count(*) as WonOpps from mytable WHERE statename = 'won' group by customername,customercontactname) a
left join
(select customername,customercontactname, count(*) as LostOpps from mytable WHERE statename = 'lost' group by customername,customercontactname) b
on a.customername = b.customername and a.customercontactname = b.customercontactname
UNION ALL
select d.customername, d.customercontactname, c.WonOpps, d.LostOpps from
(select customername,customercontactname, count(*) as WonOpps from mytable WHERE statename = 'won' group by customername,customercontactname) c
right join
(select customername,customercontactname, count(*) as LostOpps from mytable WHERE statename = 'lost' group by customername,customercontactname) d
on c.customername = d.customername and c.customercontactname = d.customercontactname ;


You may use many other options like sum, count, case to write a shorter query but this query will help you to understand the concept on top of your current work.

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy