'MYSQL: Join list of values into a table

I have list of predefined values Cash, Bank, Card, Cheque called payment modes (no db table for them).

Each payments will have it's mode.

+-----------+
+ Payments  +
+-----------+
+ id        +
+ amount    +
+ date      +
+ mode      +
+-----------+

The below query will not show mode that are not in the payments table. If for example no payment is made through cheque, then it will not be in the result.

select p.mode, SUM(p.amount) 'total'
from payments p

I also found out about Table Value Constructor but I'm not sure if its supported in MySql as I'm having syntax error.

select p.mode, SUM(p.amount)
from (
    VALUES
      ('Cash'),
      ('Card'),
      ('Cheque'),
      ('Bank')
 ) as m(name) 
 left join payments p on m.name = p.mode
group by p.mode

Is there a way to have a query that gets all the mode regardless if they are not present in the payments table? I need a result like:

++++++++++++++++++++
+ mode     | total +
++++++++++++++++++++
+ cash     | 100   +
+ cheque   | 0     +
+ bank     | 0     +
+ card     | 300   +
++++++++++++++++++++
mysqlsql


Solution 1:[1]

In MySQL, you can construct the table using union all:

select m.mode, SUM(p.amount)
from (select 'Cash' as mode union all
      select 'Card' union all
      select 'Cheque' union all
      select 'Bank'
     ) m left join
     payments p
     on m.mode = p.mode
group by m.mode;

Notes:

  • I changed name to mode so the column with the same information has the same name.
  • The group by key needs to be from the first name, not the second (that is m.mode instead of p.mode).
  • If you want 0 instead of NULL, then use coalesce(sum(p.amount), 0).
  • You might want to consider a reference table that contains the mode values.

Solution 2:[2]

select 
  m.mode, 
  SUM(p.amount) 
FROM 
  (
    SELECT 
      m.Cash as mode 
    FROM 
      (
        VALUES 
          ('cash'), 
          ('card'), 
          ('cheque'), 
          ('bank')
      ) as m
  ) as m 
  left join payments p on m.mode = p.mode 
group by 
  m.mode

You need two aliases and you must use the first value of your array (Cash) in nested select as selected column.

Solution 3:[3]

It's worth checking your version of MySQL if the VALUES approach isn't working. It was only introduced from MySQL 8.0.19 , so anyone on an earlier issue will need to use the UNION ALL approach.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 Geoff Kendall

Related Questions