asked on

How to get subquery of Aggregate

Hello,

I have a aggregate total based on transaction type...

SELECT transType AS trxType, SUM(amount) AS totSum FROM payments
WHERE result = 0
GROUP BY trxType

This will bring back results such as...

Payment, $1000
Refund, $150
etc.

How do I get a subquery of the SUM amount that will further separate the e.g. Payment $1000 further into... $200 American Express, $700 Visa, $100 MasterCard for example.

Thank you for your assistance.

deighton

you can group my the two items and also use ....WITH ROLLUP to still get the original totals and the sub totals

SOLUTION

deighton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

robnhood00

ASKER

deighton,

Thank you for the prompt reply. It works but I need to refine it a little further. If there are 4 different subtypes (e.g. Amex, Visa, MC, Discover) and I want to group JUST for AMEX and group the other 3 into one aggregate value, is there a simple way to accomplish this? The query you provided seems to provide total for EACH SubType.

REVISED Query...

SELECT transType AS trxType, CASE ccType WHEN 'A' THEN 'AMEX' ELSE 'Visa/MC/Disc' END AS ccType,
SUM(amount) AS totSum FROM paymentLogs
GROUP BY transType, ccType WITH ROLLUP
ORDER BY trxType, ABS(SUM(amount)) DESC

Thank you again for your help.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

robnhood00

ASKER

Thank you both for your help. It worked great.

deighton

thanks, good luck with your project