Derrick Derrick - 6 months ago 17
MySQL Question

MySQL Query Help on output generation making values columns

I am not that experienced with SQL or MySQL queries. I have a table named Payments. It has the data stored like shown below.

| Date | Payment-Type | Money |
+------------+--------------+---------+
| 2016-05-18 | Credit | 1619929 |
| 2016-05-18 | Debit | 35195 |
| 2016-05-18 | Cash | 10017 |
| 2016-05-18 | Points | 12 |
| 2016-05-17 | Credit | 1664529 |
| 2016-05-17 | Debit | 286404 |
| 2016-05-17 | Cash | 73244 |
| 2016-05-17 | Points | 38 |


I am having problems trying to make a query that would output the data in the following fashion shown below. I have tried to use if statements after the select but this has left me wit null entries in the other columns unrelated to the if. Any help would be great.

| Date | Credit | Cash | Debit | Points |
+------------+---------+--------+-------+--------+
| 2016-05-18 | 1619929 | 10017 | 35195 | 12 |
| 2016-05-17 | 1664529 | 286404 | 73244 | 38 |

Answer

You can use conditional aggregation for this:

SELECT Date, 
       SUM(CASE WHEN Payment-Type = 'Credit' THEN Money END) AS Credit,
       SUM(CASE WHEN Payment-Type = 'Cash' THEN Money END) AS Cash,
       SUM(CASE WHEN Payment-Type = 'Debit' THEN Money END) AS Debit,
       SUM(CASE WHEN Payment-Type = 'Points' THEN Money END) AS Points
FROM mytable
GROUP BY Date