Sub Total - 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)

Sub Total

Author

Topic

NeilC
Yak Posting Veteran

55 Posts

Posted - 2007-12-06 : 08:54:19

I have the following query:

select
L1.LookupTitle as ReferralType,
sum(ReferralTotal) as ReferralTotal

from Client_Survey_Totals T

inner join Lookup L1 on T.ReferralLookupID = L1.lookupID
inner join Client_Survey S on T.ClientSurveyID = S.ClientSurveyID

where DateRecieved Between '1/1/2006' and '1/12/2007'--@start and @end

Group By L1.LookupTitle

Which produces:

ReferralType ReferralTotal
-------------------------------------
Audiologist 9
Friend / Family 301
Presentation 2
Website 2
Hearing Aid Specialist 7
example 89
Newspaper 2
Other 11
Physician 2
Television 2
Test 2

My question is, how can I sum them up?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-06 : 09:02:59

Do you want total ReferralTotal?

Select sum(ReferralTotal) as ReferralTotal from
(
your above query
) as t

Madhivanan

Failing to plan is Planning to fail

jordanam
Yak Posting Veteran

62 Posts

Posted - 2007-12-06 : 11:14:51

Hmm... if you saw my posts, sorry.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 11:20:42

[code]
declare @t table (i int, j int)

insert @t
select 1, 1 union all
select 2, 1 union all
select 1, 2

select distinct i,
sum(j) over (partition by i) as groupsum,
sum(j) over () as total
from @t[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-07 : 00:16:01

You can use WITH CUBE clause to get total sum as a record

select
L1.LookupTitle as ReferralType,
sum(ReferralTotal) as ReferralTotal

from Client_Survey_Totals T

inner join Lookup L1 on T.ReferralLookupID = L1.lookupID
inner join Client_Survey S on T.ClientSurveyID = S.ClientSurveyID

where DateRecieved Between '1/1/2006' and '1/12/2007'--@start and @end

Group By L1.LookupTitle
WITH CUBE

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-12-07 : 01:09:51

quote:

Originally posted by Peso


declare	@t table (i int, j int)

insert	@t
select	1, 1 union all
select	2, 1 union all
select	1, 2

select distinct	i,
		sum(j) over (partition by i) as groupsum,
		sum(j) over () as total
from		@t

E 12°55'05.25"
N 56°04'39.16"

For the folks who cannot understand the new SQL 2005 syntax this is the equivalent of Peso solution with the SQL 2000 syntax if I am not wrong somewhere

declare @t table (i int, j int)

insert @t
select 1, 1 union all
select 2, 1 union all
select 1, 2

select i,sum(j)as groups,total=(select sum(j) from @t)
from @t group by i

Regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-07 : 01:10:48

quote:
Originally posted by jordanam

Hmm... if you saw my posts, sorry.

Subtotal and Grandtotals are something that should be done in your Reports. If you want to do them in sql, then you would have both detailed and summary data together

Yes. I saw your post and forgot to ask "where do you want to show your data?"

Madhivanan

Failing to plan is Planning to fail

jordanam
Yak Posting Veteran

62 Posts

Posted - 2007-12-07 : 13:05:09

I read the initial post incorrectly, and then proceeded to "correct" your first post, which was so elegantly correct.

This thread has become the perfect example of classic "Hello world" being a little overdeveloped :) That's not to say there is nothing to learn. As a matter of fact, I think I am going to grab Peso's code and play with it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources