I have the following SQL query:
SELECT
cat,
CASE WHEN CCR.id_invoice_type = 52 THEN CCR.amount END earn,
CASE WHEN CCR.id_invoice_type = 54 THEN CCR.amount END expend,
FROM fac_invoices CCR
GROUP BY CCR.cat, CCR.id_invoice_type , CCR.amount
ORDER BY CCR.cat
with the following result:
cat earn expend
=======================
3 50,4 (null)
3 (null) (null)
3 (null) 35
5 160,7 (null)
5 (null) (null)
5 (null) 35
10 50,4 (null)
10 (null) (null)
10 (null) 35
But I wanna get the current result
cat earn expend
=======================
3 50,4 35
5 160,7 35
10 50,4 35
As you can see I have tried to group the fields but it's not working. What's wrong in my query? Or which other functions should I use to get the desired result?
Thanks in advance.
You need to simplify the GROUP BY. You want only one row per cat, so that should be the only key in the GROUP BY. One way to do what you want is string_agg():
SELECT cat,
STRING_AGG(CASE WHEN CCR.id_invoice_type = 52 THEN CCR.amount END) as earn,
STRING_AGG(CASE WHEN CCR.id_invoice_type = 54 THEN CCR.amount END) as expend
FROM fac_invoices CCR
GROUP BY CCR.cat
ORDER BY CCR.cat;
Or MAX() also works in your case.
SELECT cat,
MAX(CASE WHEN CCR.id_invoice_type = 52 THEN CCR.amount END) as earn,
MAX(CASE WHEN CCR.id_invoice_type = 54 THEN CCR.amount END) as expend
FROM fac_invoices CCR
GROUP BY CCR.cat
ORDER BY CCR.cat;
You should use string_agg() then like
CASE WHEN CCR.id_invoice_type = 52 THEN string_agg(CCR.amount,',') END earn,