count/sum function query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

count/sum function query

Author

Topic

palak
Yak Posting Veteran

55 Posts

Posted - 2008-03-14 : 13:43:00

create function mytotalcount

(@audit varchar(50), @startdate datetime, @enddate datetime)

returns table

as

return

(

select t.value,sum(t.countvalue) as totalcount from
(

select

sm.value,count(sm.value) as countvalue

from subjectbase s
join stringmap sm
on s.organizationid = sm.organizationid

inner join audit a
on s.subjectid=a.subjectid

inner join incidentbase i
on i.subjectid=s.subjectid

where a.auditid= @audit and (i.modifiedon between @startdate and @enddate) and
sm.attributename = 'contractservicelevelcode' and
sm.ObjectTypeCode = 112

group by sm.value

) t

group by t.value

)

value totalcount
------------------
NHLBI Employee 329
NIH Employee 329
Public 329
VIP 329

instead of different values i m getting same...
there is something wrong in joins..can anyone help me?

thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-14 : 13:52:21

There's not enough info to correct your query, but this article will help

http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server

IT will point out some of the pitfalls when summing and counting from different tables

Jim

jemajoign
Starting Member

7 Posts

Posted - 2008-03-14 : 14:42:46

I can't tell what you're trying to do, but here's some thoughts.

You're grouping by sm.value in the subquery. So that will give you back something like:

Table t:
Value ------ CountValue
Group1 ------ count1
Group2 ------ count2
...
GroupN ------ countN

And then you are grouping the above data by t.Value in the second Group By. But notice that the data is already grouped by t.Value. So that second Group By won't in fact group anything together. Then for any given row you'll have TotalCount == t.CountValue because there is only one row in every group.

Hope that helps. And I'm no SQL expert, so someone please correct me if I'm wrong.

Thanks,
Thomas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources