Mysql remove nulls from case-CodePudding

how do i show one row per id when i run the query it staggers accross the screen

SELECT empid,
       ( CASE
           WHEN begin_date = '2020-03-13'
                AND amount THEN amount
         END ) '3/13/2020',
       ( CASE
           WHEN begin_date = '2020-03-27' THEN amount
         END ) '3/27/2020',
       ( CASE
           WHEN begin_date = '2020-04-10' THEN amount
         END ) '4/10/2020',
       ( CASE
           WHEN begin_date = '2020-04-24' THEN amount
         END ) '4/24/2020',
       ( CASE
           WHEN begin_date = '2020-05-08' THEN amount
         END ) '5/08/2020',
       ( CASE
           WHEN begin_date = '2020-05-22' THEN amount
         END ) '5/22/2020',
       ( CASE
           WHEN begin_date = '2020-06-05' THEN amount
         END ) '6/05/2020',
       ( CASE
           WHEN begin_date = '2020-06-19' THEN amount
         END ) '6/19/2020',
       ( CASE
           WHEN begin_date = '2020-07-03' THEN amount
         END ) '7/03/2020',
       ( CASE
           WHEN begin_date = '2020-07-17' THEN amount
         END ) '7/17/2020',
       ( CASE
           WHEN begin_date = '2020-07-31' THEN amount
         END ) '7/31/2020',
       ( CASE
           WHEN begin_date = '2020-08-14' THEN amount
         END ) '8/14/2020',
       ( CASE
           WHEN begin_date = '2020-08-28' THEN amount
         END ) '8/28/2020',
       ( CASE
           WHEN begin_date = '2020-09-11' THEN amount
         END ) '9/11/2020',
       ( CASE
           WHEN begin_date = '2020-09-25' THEN amount
         END ) '9/25/2020',
       ( CASE
           WHEN begin_date = '2020-10-09' THEN amount
         END ) '10/09/2020',
       ( CASE
           WHEN begin_date = '2020-10-23' THEN amount
         END ) '10/23/2020',
       ( CASE
           WHEN begin_date = '2020-11-06' THEN amount
         END ) '11/06/2020',
       ( CASE
           WHEN begin_date = '2020-11-20' THEN amount
         END ) '11/20/2020',
       ( CASE
           WHEN begin_date = '2020-12-04' THEN amount
         END ) '12/04/2020',
       ( CASE
           WHEN begin_date = '2020-12-18' THEN amount
         END ) '12/18/2020',
       ( CASE
           WHEN begin_date = '2020-12-31' THEN amount
         END ) '12/31/2020'
FROM   new_table
WHERE  amount > 0

CodePudding user response：

If you want one row per empid, you need to use sum and group by. Also, we have to be careful about summing NULL since NULL plus anything is always NULL, so we can use an else on the case statement to avoid that issue.

I'm assuming amount is nullable in your table, if that's not the case then you don't need the extra "AND amount is NOT NULL" condition.

Here's the idea:

SELECT empid,
       ( SUM(
           CASE
             WHEN begin_date = '2020-03-13'
                AND amount IS NOT NULL THEN amount
             ELSE 0
           END) ) '3/13/2020',
       .
       .
       .
FROM   new_table
WHERE  amount > 0
GROUP BY empid