question

ahelbling33 avatar image
ahelbling33 asked

Include an aggregate subquery into another query?

Hello, I am looking for some help including the following query into an existing query. My goal is to calculate the % of the entire billing statement each cardholder was responsible for. Existing query: SELECT SUM(b.TranAmt) AS TOTAL, a.LogID, b.CHolderName, b.CHolderFName FROM [CorpCard].[dbo].[Logs] a, [CorpCard].[dbo].[Trans] b WHERE (a.LogID = b.LogID AND b.StmtDate = '2013-12-24 00:00:00') AND ([CHolderFName] <> '' AND wassplit=0) GROUP BY b.StmtDate, a.LogID, b.CHolderName, b.CHolderFName ORDER BY b.CHolderName Additional query: SELECT SUM(TranAmt) AS percentTotal FROM [CorpCard].[dbo].[Trans] WHERE StmtDate = '2013-12-24 00:00:00' ![alt text][1] ![alt text][2] [1]: /storage/temp/1306-logs.jpg [2]: /storage/temp/1307-trans.jpg
aggregatessubquery
logs.jpg (18.0 KiB)
trans.jpg (46.9 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
By including this subquery, you essentially want the value returned by this appended as another column, and not another row, right? I haven't tested this, but it would look like: SELECT SUM(b.TranAmt) AS TOTAL, a.LogID, b.CHolderName, b.CHolderFName, ( SELECT SUM(TranAmt) AS percentTotal FROM [CorpCard].[dbo].[Trans] WHERE StmtDate = '2013-12-24 00:00:00' ) as percenttotal FROM [CorpCard].[dbo].[Logs] a, [CorpCard].[dbo].[Trans] b5. WHERE (a.LogID = b.LogID AND b.StmtDate = '2013-12-24 00:00:00') AND ([CHolderFName] '' AND wassplit=0) GROUP BY b.StmtDate, a.LogID, b.CHolderName, b.CHolderFName ORDER BY b.CHolderName Note that this subquery is not correlated in any way. The "percenttotal" will be the same for all rows if your outerquery winds up returning more rows. If you want them to be correlated, you need to include something in your subquery that ties in to the rows that the outer query is returning, such as perhaps the logid.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the reply. The result of your query returns a column value to each row of -21323.34 & that's as far as I got with it before asking for some help.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
You can use aggregate window functions to achieve the same. Here's a simplified example declare @trans table ( TID int, HolderName varchar(100), TranAmt money ) insert into @trans select 1, 'Alice', 10 insert into @trans select 2, 'Alice', 15 insert into @trans select 3, 'Bob', 5 insert into @trans select 4, 'Charles', 20 select distinct HolderName, sum(TranAmt)over(partition by Holdername order by Holdername) as TOTAL, sum(TranAmt)over(order by (select null)) as EntireTotal, sum(TranAmt)over(partition by Holdername order by Holdername) / sum(TranAmt)over(order by (select null)) *100 as '%age' from @trans gives the results HolderName TOTAL EntireTotal %age ------------- --------- ------------- ------- Alice 25.00 50.00 50.00 Bob 5.00 50.00 10.00 Charles 20.00 50.00 40.00
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.