Mysql Conditional Aggregation Using group_by query and Case to group Month and Year

I’ve a table structure where I’m saving the data in the below format.

The ledger amount needs to be divided into two columns and grouped through the sum equation.

Those rows having receipt_id not as null their ledger_amount needs to be displayed as sum(ledger_amount) as credit whole rows having payment_id not as null their ledger_amount needs to be displayed as sum(ledger_amount) as debit

These values need to be grouped using the “month-year” fetched from the date from the receipt_master and payment_master tables using join using the “date period”.

I’m quite successful in developing the overall query except one minor glitch.

When I use the “group_by” query on “month-year” it sums up all the record of the particular month in either debit/credit depending on the higher number of rows in the particular criteria, which I want to be separate.

Check this image.

I’ve not yet applied group_by month on this currently, it is showing individual records perfectly.

See below what happens when I apply group_by via month.

It sums up all the ledger_amount values and display it on the debt side as 53819.00 instead of showing 19500.00 on credit side and 34319.00 on the debit side

Below are my queries for the above results.

Query for the 2nd Image

SELECT sjm.company_id as sjm_company_id,
    (CASE when sjm.payment_id IS NOT NULL then sum(sjm.ledger_amount) END) AS "CREDIT",
    (CASE when sjm.receipt_id IS NOT NULL then sum(sjm.ledger_amount) END) AS "DEBIT",

    (CASE when sjm.payment_id IS NOT NULL then date_format(sim.payment_date,'%Y-%m')
          when sjm.receipt_id IS NOT NULL then date_format(rim.receipt_date,'%Y-%m') END) AS s_year_month

    FROM ledger_mapper_master sjm
    LEFT JOIN payment_master sim ON sjm.payment_id = sim.payment_id
    LEFT JOIN receipt_master rim ON sjm.receipt_id = rim.receipt_id

    WHERE sjm.sundry_dr_cr_ledger_id IS NOT NULL AND sjm.ledger_id='5991'
    group by ledger_mapper_id
    HAVING s_year_month = '2020-05' AND sjm_company_id = '1' ORDER BY s_year_month;

Query for the 3rd image – both are same with a small change in group_by clause to group with s_year_month

SELECT sjm.company_id as sjm_company_id,
    (CASE when sjm.payment_id IS NOT NULL then sum(sjm.ledger_amount) END) AS "CREDIT",
    (CASE when sjm.receipt_id IS NOT NULL then sum(sjm.ledger_amount) END) AS "DEBIT",

    (CASE when sjm.payment_id IS NOT NULL then date_format(sim.payment_date,'%Y-%m')
          when sjm.receipt_id IS NOT NULL then date_format(rim.receipt_date,'%Y-%m') END) AS s_year_month

    FROM ledger_mapper_master sjm
    LEFT JOIN payment_master sim ON sjm.payment_id = sim.payment_id
    LEFT JOIN receipt_master rim ON sjm.receipt_id = rim.receipt_id

    WHERE sjm.sundry_dr_cr_ledger_id IS NOT NULL AND sjm.ledger_id='5991'
    group by s_year_month
    HAVING s_year_month = '2020-05' AND sjm_company_id = '1' ORDER BY s_year_month;

Please show me what change I need to do to keep the debit section and credit section amount in their respective columns instead of both getting merged.

i.e. the values here should be debit_amount:34319 and credit_amount:19500 in that single row.

Answer

I think you want conditional aggregation:

SELECT sjm.company_id as sjm_company_id,
       (CASE WHEN sjm.payment_id IS NOT NULL then date_format(sim.payment_date, '%Y-%m')
             WHEN sjm.receipt_id IS NOT NULL then date_format(rim.receipt_date, '%Y-%m')
        END) AS s_year_month
       SUM(CASE when sjm.payment_id IS NOT NULL then sjm.ledger_amount) END AS CREDIT,
       SUM(CASE when sjm.receipt_id IS NOT NULL then sjm.ledger_amount) END AS DEBIT    
FROM ledger_mapper_master sjm LEFT JOIN
     payment_master sim
     ON sjm.payment_id = sim.payment_id LEFT JOIN
     receipt_master rim
     ON sjm.receipt_id = rim.receipt_id
WHERE sjm.sundry_dr_cr_ledger_id IS NOT NULL AND
      sjm.ledger_id = 5991 AND
      sjm_company_id = 1
GROUP BY sjm_company_id, s_year_month
HAVING s_year_month = '2020-05'
ORDER BY s_year_month;

Notes:

• The conditional aggregation has the CASE as an argument to the aggregation function.
• The GROUP BY keys should match the unaggregated columns int he SELECT.
• The “id” columns look like numbers, so I removed the single quotes for the comparisons. If they are really strings, then use single quotes.

Also, the s_year_month calculation could be simplified to:

DATE_FORMAT(COALESCE(sim.payment_date, rim.receipt_date) '%Y-%m') AS s_year_month

But I let your version above.