CASE issue with MYSQL

Question

I have a table that stores transactions between a buyer and a supplier, transactions are categorized by a "doctype" denoting credit note or invoice. I'm trying to create a view that shows a legder-like view of the same transactions.

SELECT
SUM(CASE doctype WHEN doctype = 1 THEN docvalue END) AS Invoice, 
SUM(CASE doctype WHEN doctype = 2 THEN docvalue END) AS CreditNote, 
SUM(CASE doctype WHEN doctype = 3 THEN docvalue END) AS JournalEntry, 
SUM(CASE doctype WHEN doctype = 4 THEN docvalue END) AS Payment
FROM transactions 
group by buyer

When I run this query I see a result for the first CASE statement however the rest return null values. Is there a better way to do this?

My goal is to have this as a view but I can't even get the query to show what I want. I also tried the below as a terrible hack but it doesn't even work

SELECT
(sum(docvalue)
    FROM transactions where doctype =1) as invoices,
(sum(docvalue)
    FROM transactions where doctype =2) as creditnotes,
(sum(docvalue)
    FROM transactions where doctype =3) as journals,
(sum(docvalue)
    FROM transactions where doctype =4) as payments
from transactions

I'd tear my hair out but I'm bald!

There are two forms of the CASE expression. "CASE WHEN condition THEN ..." and "CASE expression WHEN value THEN ...". — spencer7593
– spencer7593, Commented Jul 22, 2016 at 15:04

Jocelyn · Accepted Answer · 2016-07-22 14:59:04Z

1

The syntax for CASE is incorrect. Here is a modified version:

SELECT
SUM(CASE doctype WHEN 1 THEN docvalue END) AS Invoice, 
SUM(CASE doctype WHEN 2 THEN docvalue END) AS CreditNote, 
SUM(CASE doctype WHEN 3 THEN docvalue END) AS JournalEntry, 
SUM(CASE doctype WHEN 4 THEN docvalue END) AS Payment
FROM transactions 
group by buyer

answered Jul 22, 2016 at 14:59

Jocelyn

11.4k11 gold badges47 silver badges63 bronze badges

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

2 Comments

Daniel Over a year ago

Thank you, I'm mortified at how obvious that was!

Daniel Over a year ago

Would you have a suggested method of performing calculations against the values as a "total" column?

Collectives™ on Stack Overflow

CASE issue with MYSQL

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related