need help -- multiple agg functions? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

need help -- multiple agg functions?

Author

Topic

melisande
Starting Member

2 Posts

Posted - 2008-01-17 : 14:53:59

Hi there -- new to SQL and new to SQL Team. What a combo!

I'm writing in T-SQL within MS Reporting Services. I have a dataset that I arrived at using a fairly complicated JOIN. Say it looks something like this:

ID   Dept  Type  Total
----------------------
AA   XXX   U    100.00
AA   XXX   U     50.00
AA   ZZZ   U    150.00
AA   XXX   O    400.00
BB   QQQ   O     75.00

Is there a way to tweak my results so that I can perform multiple aggregate functions on parts of the data? What I mean is, can I get a COUNT(*) and SUM(Total) grouped for all ID/Dept/U and ID/Dept/O on the same row? Like:

ID   Dept  #U   TotalU   #O   TotalO
------------------------------------
AA   XXX   2    150.00   1    400.00
AA   ZZZ   1    150.00   0
BB   QQQ   0             1     75.00

THANKS!!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-17 : 14:55:48

You can use the GROUP'ing in the report..and use SUM() in the GROUP FOOTER that will be easier..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

melisande
Starting Member

2 Posts

Posted - 2008-01-18 : 12:35:58

I've tried to use GROUP BY, and the best I can come up with looks something like this:

SELECT
ID, Dept, Type, COUNT(*) AS Count, SUM(Total) AS Amount
FROM [dataset]
GROUP BY ID, Dept, Type

which ends up looking like:

ID Dept Type Count Amount
---------------------------------
AA XXX U 2 150.00
AA XXX O 1 400.00
AA ZZZ U 1 150.00
BB QQQ O 1 75.00

which is at least correct, but I've got to have them formatted in the report as above, with Us and Os for each ID/Dept combination on the same line. I don't know of a way to fudge that using report formatting.

I've also tried to say

SELECT
ID, Dept,
(COUNT(*) WHERE Type = 'U') AS '#U',
(SUM(Total) WHERE Type = 'U') AS 'TotalU',
(COUNT(*) WHERE Type = 'O') AS '#O',
(SUM(Total) WHERE Type = 'O') AS 'TotalO'
FROM [dataset]
GROUP BY ID, Dept

This does not give me the correct results either: where it says [dataset], there's actually a join with a three-condition WHERE clause. The aggregate subqueries are executed BEFORE the larger query, when I intend them to be executed on the result of the larger query. And if I try to put more parameters in the aggregate subqueries, it complains if I don't also put the same parameters in the GROUP BY on the larger query. If I do that, it gives me data that's too granular for my purpose.

Did I mention I'm new at all this? :( Should I be supplying more/different information?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-18 : 12:44:08

Does this worok:



Declare @T Table (ID varchar(5), Dept varchar(10), Type char(1), Total decimal(10,2))
Insert into @T
Select 'AA',   'XXX' ,  'U',    100.00 Union all
Select 'AA',   'XXX' ,  'U',     50.00 Union all
Select 'AA',   'ZZZ' ,  'U',    150.00 Union all
Select 'AA',   'XXX' ,  'O',    400.00 Union all
Select 'BB',   'QQQ' ,  'O',     75.00 

Select min(ID) Id,  Dept, 
	SUM(Case when [Type] = 'U' Then 1 ELSE 0 END) As #U, 
	SUM(Case when [Type] = 'U' Then Total ELSE 0 END) As TotalU,
	SUM(Case when [Type] = 'O' Then 1 ELSE 0 END) As #O,
	SUM(Case when [Type] = 'O' Then Total ELSE 0 END) As TotalO
From @T T
Group by T.Dept
Order by 1


(5 row(s) affected)
Id    Dept       #U          TotalU                                  #O          TotalO
----- ---------- ----------- --------------------------------------- ----------- ---------------------------------------
AA    XXX        2           150.00                                  1           400.00
AA    ZZZ        1           150.00                                  0           0.00
BB    QQQ        0           0.00                                    1           75.00

(3 row(s) affected)

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources