Postgres GROUP BY, SUBSTRING and SUM Query Issue

Question

I am trying to write a GROUP BY query but I am struggling to achieve the desired result. I am including

A JSON object that represent the DB table with some data.

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"
    }
]

My current query and its result.

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"
        }
    ]

The desired result.

`"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.

score 2 · Accepted Answer · answered Feb 15 '18 at 19:56

2

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.

answered Feb 15 '18 at 19:56

dnswlt

2,925
19
15

Thank you @dnswit. The result I get from you query looks great. The only other thing I would like is if it could sum the amount of the the records with the same payment_type and gl_code substring. Currently they are still separate. – Marius Feb 15 '18 at 20:15
What do you mean exactly? Do you want to sum over all rows with identical gl_code substring and payment_type and not distinguish by activity_id, description, etc.? Then leave them our of the `select` and `group by` expression lists. – dnswlt Feb 15 '18 at 20:25
Flip now I see my mistake. Thank you for pointing it out to me and very quick responses @dnswit. – Marius Feb 15 '18 at 20:31

Postgres GROUP BY, SUBSTRING and SUM Query Issue

1 Answers1