MYSQL SUM(IF) with group by NAME but some NAME wanna makes sum

user_3867315 0
Viewed 603

I have one question in SQL.

This is the query.

SELECT Customer,
        SUM(IF(date_format(OutputDate, '%Y') = 2020 AND date_format(OutputDate, '%m') = 3, SupplyPrice, 0)) AS March
    FROM (SELECT Customer, OutputDate, SupplyPrice FROM `TableOutput`) AS C
    GROUP BY Customer
    ORDER BY March DESC
  LIMIT 20;

it makes a result below:

Customer       March
A              100
B              200
C              300
D              400
...

but I wanna make Customer A and B sum together as A(not B) or new name(e.g. Z)

like this:

Customer       March
A(or Z)        300
C              300
D              400
...

I want to modify the query and output it.

count group-by mysql sql database
1 Answers

I understand that you want to group together customers A and B. You can do this with a CASE expression:

SELECT 
    (CASE WHEN customer IN ('A', 'B') THEN 'A' ELSE Customer
     END) as real_customer,
    SUM(CASE WHEN outputDate >= '2020-03-01' AND outputDate < '2020-04-01' THEN SupplyPrice ELSE 0 END) AS March
FROM `TableOutput`
GROUP BY real_customer
ORDER BY March DESC
LIMIT 20;

Note that I modified the date predicate on outputDate so it uses comparisons to date litterals rather than using date functions; this is a more efficient approach, that may take advantage of a possibly existing index.

user_106767160
Related