CODEWITHSUNDEEP
mysqlsql
Ask Warvin
Ask Warvin

Reputation: 63

Display all the values of each category per date

I am creating report from temporary table to generate data per date with each category
Here's the example data and table 

| Category  | Percentage   | Date       |
| Assets    | 12.89        | 2020-01-31 |
| Revenue   | 34.78        | 2020-01-31 |
| Expenses  | 30.88        | 2020-01-31 |

and here's the mysql query I tried 

Select Date,
(CASE WHEN Category='Assets' THEN Percentage ELSE 0 END) AS asset,
(CASE WHEN Category='Revenue' THEN Percentage ELSE 0 END) AS income,
(CASE WHEN Category='Expenses' THEN Percentage ELSE 0 END) AS expense
from tbl_temporary1 WHERE Date='2020-01-31'

Instead of one row result, it become something like this 

| Date       | asset  | income  | expense  |
| 2020-01-31 | 12.89  | 0.00    | 0.00     |
| 2020-01-31 | 0.00   | 34.78   | 0.00     |
| 2020-01-31 | 0.00   | 0.00    | 30.88    |

Result Expected 

| Date       | asset  | income  | expense  |
| 2020-01-31 | 12.89  | 34.78   | 30.88    |

Hope you could help me to fix, I tried to join the table by itself but same results. Thanks

Upvotes: 0

Views: 148

Answers (2)

ysth
ysth

Reputation: 98398

(no subquery is needed here)

You almost had it right. Add sums and group by:

Select Date,
SUM(CASE WHEN Category='Assets' THEN Percentage ELSE 0 END) AS asset,
SUM(CASE WHEN Category='Revenue' THEN Percentage ELSE 0 END) AS income,
SUM(CASE WHEN Category='Expenses' THEN Percentage ELSE 0 END) AS expense
from tbl_temporary1
WHERE Date='2020-01-31'
GROUP BY Date

If only one date is selected, the GROUP BY can be omitted, since the use of an aggregate function (SUM) makes it default to grouping all rows together.

Upvotes: 3

Amit Verma
Amit Verma

Reputation: 2490

Try below query and let me know if you face any issue.

SELECT Date, SUM(asset)asset, SUM(income)income, SUM(expense) FROM
(
Select Date,
(CASE WHEN Category='Assets' THEN Percentage ELSE 0 END) AS asset,
(CASE WHEN Category='Revenue' THEN Percentage ELSE 0 END) AS income,
(CASE WHEN Category='Expenses' THEN Percentage ELSE 0 END) AS expense
from tbl_temporary1 WHERE Date='2020-01-31')temp
GROUP BY Date

Upvotes: 1

Related Questions