aggregate functions - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

aggregate functions

Author

Topic

shemayb
Posting Yak Master

159 Posts

Posted - 2007-11-29 : 13:23:28

Can i combine two aggregate functions in one select statement?Like sum(count (field 1) * field 2) as tcost.Can i do something like that in my query?

thanks!

Funnyfrog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-29 : 13:27:42

Yes you can have more than one aggregate function in one query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shemayb
Posting Yak Master

159 Posts

Posted - 2007-11-29 : 13:30:55

i tried it but it didn't work..

Funnyfrog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-29 : 13:35:15

Saying "it didn't work" doesn't help us help you. Please provide the error and the full query that you tried.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shemayb
Posting Yak Master

159 Posts

Posted - 2007-11-29 : 13:37:51

Select sum(count(cp.pid) * cp.mr) as ptcost from caregiverpayroll cp
inner join visittype vt on vt.tid = cp.tid
inner join visits v on v.apid = cp.apid
Where actual_visit_date between '01/01/2000' and '12/31/2007'
AND active=1 AND visited=1 and vt.ctid = '421'
Group by cp.pid,cp.mr

that is my query..

Funnyfrog

shemayb
Posting Yak Master

159 Posts

Posted - 2007-11-29 : 13:38:42

this is the error of the query that i previously poaste:Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Funnyfrog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-29 : 13:45:12

[code]
Select sum(ptcost) as sumptcost
from
(
select (count(cp.pid) * cp.mr) as ptcost
from caregiverpayroll cp
inner join visittype vt on vt.tid = cp.tid
inner join visits v on v.apid = cp.apid
Where actual_visit_date between '01/01/2000' and '12/31/2007'
AND active=1 AND visited=1 and vt.ctid = '421'
Group by cp.pid,cp.mr
) t
[/code]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shemayb
Posting Yak Master

159 Posts

Posted - 2007-11-29 : 13:49:32

thanks! i will try it..

Funnyfrog

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-29 : 14:00:44

There's a whole article on this:

http://www.sqlteam.com/article/aggregating-correlated-sub-queries

- Jeff
http://weblogs.sqlteam.com/JeffS

shemayb
Posting Yak Master

159 Posts

Posted - 2007-11-29 : 14:20:00

Select sum(ptcost) as sumptcost,sum(ptvisits)
from
(
select count(cp.cpid)as ptvisits,(count(cp.pid) * cp.mr) as ptcost
from caregiverpayroll cp
inner join visittype vt on vt.tid = cp.tid
inner join visits v on v.apid = cp.apid
Where actual_visit_date between '01/01/2000' and '12/31/2007'
AND active=1 AND visited=1 and vt.ctid = '421'
Group by cp.pid,cp.mr
) t

if i have the query above,can i add another select statement above all of these to get the average of sum(ptcost) as sumptcost and sum(ptvisits)?

Funnyfrog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-29 : 14:35:12

You can get average of sum(ptcost), but you can't get sum(ptvisits) since it isn't being calculated in the derived table.

If you'd provide a data example of what the inner query produces and then show us exactly what you want outputted, we can help provide a working solution.

Also, make sure you read the article Jeff posted rather than just asking us questions.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources