Solved

How to get subquery of Aggregate

Posted on 2013-02-05
6
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 125 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 125 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.

734 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