Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to get subquery of Aggregate

Posted on 2013-02-05
6
Medium Priority
?
275 Views
Last Modified: 2013-02-05
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.
0
Comment
Question by:robnhood00
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 38854134
you can group my the two items and also use ....WITH ROLLUP to still get the original totals and the sub totals
0
 
LVL 18

Assisted Solution

by:deighton
deighton earned 500 total points
ID: 38854142
SELECT transType AS trxType,  YourSubType , SUM(amount) AS totSum FROM payments
WHERE result = 0
GROUP BY trxType, YourSubType  WITH ROLLUP
0
 

Author Comment

by:robnhood00
ID: 38854187
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38854197
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
 

Author Closing Comment

by:robnhood00
ID: 38854229
Thank you both for your help.  It worked great.
0
 
LVL 18

Expert Comment

by:deighton
ID: 38854235
thanks, good luck with your project
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question