I'm facing a challenge with an SQL query in Entrinsik's Informer. I need to calculate a percentage using JavaScript on the result, but unfortunately, Informer cannot access data down columns (such as the total for the percentage). Therefore, I have to modify my SQL query to generate this total. The current output is:
refund_code refund_amount month_group
----------- ------------- -----------
ref1 10 january
ref2 20 january
ref3 30 january
ref1 40 february
ref2 50 february
ref3 60 february
What I am aiming for is something like this:
refund_code refund_amount month_group month_total
----------- ------------- ----------- -----------
ref1 10 january 60
ref2 20 january 60
ref3 30 january 60
ref1 40 february 150
ref2 50 february 150
ref3 60 february 150
This led me to the following query:
SELECT mr.month_group,
bd.transaction_code AS refund_code,
SUM(bd.extended) AS refund_amount
FROM billing_details AS bd
LEFT JOIN monthly_ranges AS mr
ON ( bd.entry_date BETWEEN mr.start_date AND mr.end_date )
WHERE bd.transaction_code IN ( 'REFPRI', 'REFSEC', 'REFPT', 'REFREQPRI' )
AND bd.entry_date >= '2012-01-05'
GROUP BY mr.month_group, bd.transaction_code
ORDER BY mr.month_group, bd.transaction_code
In addition, I created a second query to obtain totals per month:
SELECT mr.month_group,
SUM(bd.extended) AS refund_amount
FROM billing_details AS bd
LEFT JOIN monthly_ranges AS mr
ON ( bd.entry_date BETWEEN mr.start_date AND mr.end_date )
WHERE bd.transaction_code IN ( 'REFPRI', 'REFSEC', 'REFPT', 'REFREQPRI' )
AND bd.entry_date >= '2012-01-05'
GROUP BY mr.month_group
ORDER BY mr.month_group
Now, I'm exploring ways to merge these two queries together. Any suggestions?