MySQL Query Help on output generation making values columns

Question

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 |

Giorgos Betsos · Accepted Answer · 2016-05-20 15:00:29Z

1

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

answered May 20, 2016 at 15:00

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2016-05-20 15:00:51Z

1

You can do this with conditional aggregation:

select date,
       sum(case when paytype = 'Credit' then money else 0 end) as credit,
       sum(case when paytype = 'Cash' then money else 0 end) as cash,
       sum(case when paytype = 'Debit' then money else 0 end) as debit,
       sum(case when paytype = 'Points' then money else 0 end) as points
from payments
group by date
order by date;

answered May 20, 2016 at 15:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MySQL Query Help on output generation making values columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related