?
Solved

How to get subquery of Aggregate

Posted on 2013-02-05
6
Medium Priority
?
271 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 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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 extract information from SQL Server on Database, Connection and Server properties

719 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