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.
robnhood00Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you have 2 options:
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, CASE ccType WHEN 'A' THEN 'AMEX' ELSE 'Visa/MC/Disc' ENDWITH ROLLUP
ORDER BY trxType, ABS(SUM(amount)) DESC  

Open in new window


or this, if you want it row-wise
SELECT transType AS trxType
,SUM(CASE ccType WHEN 'A'  THEN amount ELSE 0 END ) AS totSumAmex
,SUM(CASE ccType WHEN 'A'  THEN 0 ELSE amount END ) AS totSumVisaMCDisc
,SUM(amount ) AS totSum 
FROM paymentLogs
GROUP BY transType 
ORDER BY trxType, ABS(SUM(amount)) DESC  

Open in new window

0
 
deightonprogCommented:
you can group my the two items and also use ....WITH ROLLUP to still get the original totals and the sub totals
0
 
deightonConnect With a Mentor progCommented:
SELECT transType AS trxType,  YourSubType , SUM(amount) AS totSum FROM payments
WHERE result = 0
GROUP BY trxType, YourSubType  WITH ROLLUP
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
robnhood00Author Commented:
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.
0
 
robnhood00Author Commented:
Thank you both for your help.  It worked great.
0
 
deightonprogCommented:
thanks, good luck with your project
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.