Home > OS >  MySQL group by with condition
MySQL group by with condition

Time:12-16

I have a problem of MySQL group by. I have a field called 'type'. I would like to make a group by of this field. Here is the examples.

Amount type
30 disable
50 V123
40 AGA
100 V2594
30 disable
40 school

I would like to have the following group by

type Amount
disable 60
VIP 150
AGA 40
school 40

which mean I would like to check if the type is 'V' for prefix. If it is 'V' prefix and the records will group together with new name 'VIP'. Otherwise just keep the same name.

Thanks you a lot.

CodePudding user response:

We can use conditional aggregation here:

SELECT
    CASE WHEN type LIKE 'V%' THEN 'VIP' ELSE type END AS type,
    SUM(Amount) AS amount
FROM yourTable
GROUP BY 1;
  • Related