使用Group BY将多个Select语句放入不同的列

Question

我有这个表，为了简单起见，我只包含了一个客户名称，它有很多

+--------------+---------------------+---------------+
| 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           |
+--------------+---------------------+---------------+

我想要这个输出

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

试用(前3行的结果很好，但是Amelia没有显示在我的最终输出中)：

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 

Answer 1

使用条件聚合：

   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

Answer 2

使用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

Answer 3

您可以将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返回NULL，因为我们没有指定ELSE-values。

如果您希望看到0，那么可以添加ELSE 0，也可以使用COUNT而不是SUM。