I am trying to write a GROUP BY query but I am struggling to achieve the desired result. I am including
SELECT * FROM tb_transaction "tb_transaction": [ { "id": "121", "profile_id": "57", "event_id": "45", "activity_id": "67", "payment_type": "EFT", "transaction_type": "activity_registration", "gl_code": "1234-56-102-020", "description": "Golf", "amount": "1500", "paid": "f", "invoice_number": "0006" }, { "id": "117", "profile_id": "57", "event_id": "45", "activity_id": "65", "payment_type": "EFT", "transaction_type": "activity_registration", "gl_code": "1234-56-102-056", "description": "Cuppa", "amount": "100", "paid": "f", "invoice_number": "0006" }, { "id": "120", "profile_id": "57", "event_id": "45", "activity_id": "70", "payment_type": "EFT", "transaction_type": "activity_registration", "gl_code": "1234-13-102-064", "description": "Nutrition & Lifestyle", "amount": "510", "paid": "f", "invoice_number": "0006" }, { "id": "125", "profile_id": "207", "event_id": "45", "activity_id": "65", "payment_type": "Cash", "transaction_type": "activity_registration", "gl_code": "1234-56-102-056", "description": "Cuppa", "amount": "100", "paid": "f", "invoice_number": "0007" }, { "id": "126", "profile_id": "207", "event_id": "45", "activity_id": "65", "payment_type": "Cash", "transaction_type": "merchandise", "gl_code": "3400-56-102-056", "description": "Cap", "amount": "20", "paid": "f", "invoice_number": "0007" }, { "id": "128", "profile_id": "193", "event_id": "45", "activity_id": "70", "payment_type": "SnapScan", "transaction_type": "activity_registration", "gl_code": "1234-13-102-064", "description": "Nutrition & Lifestyle", "amount": "510", "paid": "f", "invoice_number": "0008" }, { "id": "131", "profile_id": "193", "event_id": "45", "activity_id": "65", "payment_type": "SnapScan", "transaction_type": "merchandise", "gl_code": "3400-56-102-056", "description": "Water Bottle", "amount": "10", "paid": "f", "invoice_number": "0008" }, { "id": "130", "profile_id": "193", "event_id": "45", "activity_id": "65", "payment_type": "SnapScan", "transaction_type": "activity_registration", "gl_code": "1234-56-102-056", "description": "Cuppa", "amount": "100", "paid": "f", "invoice_number": "0008" } ]
SELECT gl_code, transaction_type, activity_id, payment_type, description, SUM(amount) AS amount FROM tb_transaction WHERE event_id = 45 AND paid = false GROUP BY gl_code, transaction_type, activity_id, payment_type, description ORDER BY gl_code; "RECORDS": [ { "gl_code": "1234-13-102-064", "transaction_type": "activity_registration", "activity_id": "70", "payment_type": "EFT", "description": "Nutrition & Lifestyle", "amount": "510" }, { "gl_code": "1234-13-102-064", "transaction_type": "activity_registration", "activity_id": "70", "payment_type": "SnapScan", "description": "Nutrition & Lifestyle", "amount": "510" }, { "gl_code": "1234-56-102-056", "transaction_type": "activity_registration", "activity_id": "65", "payment_type": "Cash", "description": "Cuppa", "amount": "100" }, { "gl_code": "1234-56-102-056", "transaction_type": "activity_registration", "activity_id": "65", "payment_type": "EFT", "description": "Cuppa", "amount": "100" }, { "gl_code": "1234-56-102-056", "transaction_type": "activity_registration", "activity_id": "65", "payment_type": "SnapScan", "description": "Cuppa", "amount": "100" }, { "gl_code": "1234-56-102-020", "transaction_type": "activity_registration", "activity_id": "67", "payment_type": "EFT", "description": "Golf", "amount": "1500" }, { "gl_code": "3400-56-102-056", "transaction_type": "merchandise", "activity_id": "65", "payment_type": "Cash", "description": "Cap", "amount": "20" }, { "gl_code": "3400-56-102-056", "transaction_type": "merchandise", "activity_id": "65", "payment_type": "SnapScan", "description": "Water Bottle", "amount": "10" } ]
`"RECORDS": [ { "gl_code": "1234-13-102-064", "transaction_type": "activity_registration", "activity_id": "70", "payment_type": "EFT", "description": "Nutrition & Lifestyle", "amount": "510" }, { "gl_code": "1234-13-102-064", "transaction_type": "activity_registration", "activity_id": "70", "payment_type": "SnapScan", "description": "Nutrition & Lifestyle", "amount": "510" }, { "gl_code": "1234-56-102-056", "transaction_type": "activity_registration", "activity_id": "65", "payment_type": "Cash", "description": "Cuppa", "amount": "120" }, { "gl_code": "1234-56-102-056", "transaction_type": "activity_registration", "activity_id": "65", "payment_type": "EFT", "description": "Cuppa", "amount": "100" }, { "gl_code": "1234-56-102-056", "transaction_type": "activity_registration", "activity_id": "65", "payment_type": "SnapScan", "description": "Cuppa", "amount": "110" }, { "gl_code": "1234-56-102-020", "transaction_type": "activity_registration", "activity_id": "67", "payment_type": "EFT", "description": "Golf", "amount": "1500" } ]'
The difference is very subtle. But you will see that I basically want to sum the records who's "payment_type" and last 3 digits of the "gl_code" are the same. For example "payment_type": "Cash" and "gl_code": "1234-56-102-056",
Any help will be immensely appreciated.
If you want to group by payment_type and last 3 digits of gl_code (and apparently some other columns), you can do
SELECT substring(gl_code, length(gl_code) - 3),
transaction_type,
activity_id,
payment_type,
description,
SUM(amount) AS amount
FROM tb_transaction
WHERE event_id = 45 AND paid = false
GROUP BY substring(gl_code, length(gl_code) - 3),
transaction_type,
activity_id,
payment_type,
description
ORDER BY gl_code;
Note however that this will only select the last 3 digits, and not an (arbitrary) gl_code from all those that share the same last 3 digits.