Sum Of Sum - 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)

Sum Of Sum

Author

Topic

ravininave
Posting Yak Master

111 Posts

Posted - 2010-06-25 : 15:02:28

Hi All,
This is My Table

Id Total
1 1
2 1
1 1
4 1
1 1
6 1
1 1
8 1
6 1
8 1
8 1

In above case 1 has a total of 4, means 1 has came 4 times means if I group by on id the output would be

1 4
2 1
4 1
6 2
8 3

Now I've to sum the total like
if Total is greater than 2 then 2 else total

Means it could be something like
sum(Case when sum(Total) > 2 then 2 else sum(Total) end)
But it won't work

In short I've to sum the total for those ID which are lesser than two or equal to two and if total is greater than two then it should assume it as 2.

How could I do it.

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

hai
Yak Posting Veteran

84 Posts

Posted - 2010-06-25 : 15:19:29

something like this?

select id, (case when sum(total) >= 2 then 2 else sum(total) end) from
(
select 1 as id, 1 as total
union all
select 2, 1
union all
select 1, 1
union all
select 4, 1
union all
select
1, 1
union all
select
6, 1
union all
select
1, 1
union all
select
8, 1
union all
select
6, 1
union all
select
8, 1
union all
select
8 ,1
)B
group by id

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-25 : 16:22:22

no need to "case" or double sum or anything else.
see this example

select id, sum(total)
from (
select id, total, row_number() over (partition by id order by total) AS recid
from yourtablenamehere
) AS d
where recid <= 2
group by id

N 56°04'39.26"
E 12°55'05.63"

ravininave
Posting Yak Master

111 Posts

Posted - 2010-06-25 : 19:41:51

quote:
Originally posted by Peso

no need to "case" or double sum or anything else.
see this example

select id, sum(total)
from (
select id, total, row_number() over (partition by id order by total) AS recid
from yourtablenamehere
) AS d
where recid <= 2
group by id

N 56°04'39.26"
E 12°55'05.63"

No it doesn't works
the output should be 8 Only.
I just wanna Total.

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

ravininave
Posting Yak Master

111 Posts

Posted - 2010-06-25 : 20:01:58

See What I'd done but there should be some simple sql statement

DECLARE @DateTable AS TABLE (ID int,Total int,FinalTotal int)

Insert into @DateTable
Select ID,sum(Total) as sTotal,Case When sum(Total) >2 then 2 else sum(Total) end as FinalTotal from MyTableName Group by ID Order by sTotal

Select sum(FinalTotal) from @DateTable;

Output =8

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-26 : 01:37:51

[code];with cte(id, total) AS (select id, sum(total)
from (
select id, total, row_number() over (partition by id order by total) AS recid
from yourtablenamehere
) AS d
where recid <= 2
group by id)
select sum(total) from cte[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources