Solved

How to get subquery of Aggregate

Posted on 2013-02-05
6
260 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access denied running PowerPivot -SQL Server 2014 on Windows Server 2012 10 28
MS SQL with ODBC 5 35
Run SQL Server Proc from Access 11 29
TSQL - How to declare table name 26 29
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 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