Daniel Daniel - 4 months ago 25
SQL Question

CASE issue with MYSQL

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!

Answer

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
Comments