to sum the records excluding the top n records

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 2008 Forums

Transact-SQL (2008)

to sum the records excluding the top n records

Author

Topic

ayadav0984
Starting Member

21 Posts

Posted - 2011-06-01 : 03:29:00

How can we sum the records in a query excluding the top n records ?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-06-01 : 04:30:22

--n = 4 for example
Select sum(myval) from
(
Select row_number() over (Order by MyCol) as Srno, MyCol as Myval
from MyTable ) as SubTab
where Srno > 4

ayadav0984
Starting Member

21 Posts

Posted - 2011-06-01 : 04:56:55

Thanks for the reply . IS there a way to do it without rownum .Actually i was doing it in SQL server and not oracle.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-06-01 : 05:20:01

SQL Server 2005 and higher version have this function.

ayadav0984
Starting Member

21 Posts

Posted - 2011-06-01 : 05:54:06

select top 10 brand,
isnull(sum(case when TrnxMnth='APR-11' and current_status='order shipped' and product_cat='ELECTRONICS - MOBILES' then suborderamount end)/100000,0) as [GSV_APR-11],
isnull(sum(case when TrnxMnth='APR-11' and current_status='order shipped' and product_cat='ELECTRONICS - MOBILES' then MIS_Margin end)/100000,0) as [MARGIN_APR-11]
from dbo.COMMISSION_DB_APR10_APR11 where brand is not null group by brand order by [GSV_APR-11] desc

I want another row in the result with the sum of all brands except the top 10 brands...please provide a way to do so..

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-06-01 : 06:34:19

I have not carried out testing. Try the below statement and let us know.

;With MyCTE as
(
select Row_number() Over (Partitioned by brand order by [GSV_APR-11] desc) as Srno
isnull(sum(case when TrnxMnth='APR-11' and current_status='order shipped' and product_cat='ELECTRONICS - MOBILES' then suborderamount end)/100000,0) as [GSV_APR-11],
isnull(sum(case when TrnxMnth='APR-11' and current_status='order shipped' and product_cat='ELECTRONICS - MOBILES' then MIS_Margin end)/100000,0) as [MARGIN_APR-11]
from dbo.COMMISSION_DB_APR10_APR11 where brand is not null group by brand --order by [GSV_APR-11] desc
)

Select [GSV_APR-11],[MARGIN_APR-11] from MyCTE where Srno <=10
union
Select Sum([GSV_APR-11]) , sum([MARGIN_APR-11]) from MyCTE where Srno > 10

ayadav0984
Starting Member

21 Posts

Posted - 2011-06-01 : 07:02:59

I am getting an error as : Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Partitioned'.

Please tell what the problem is ..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-01 : 07:15:50

quote:
Originally posted by pk_bohra

I have not carried out testing. Try the below statement and let us know.

;With MyCTE as
(
select Row_number() Over (Partitioned by brand order by [GSV_APR-11] desc) as Srno
isnull(sum(case when TrnxMnth='APR-11' and current_status='order shipped' and product_cat='ELECTRONICS - MOBILES' then suborderamount end)/100000,0) as [GSV_APR-11],
isnull(sum(case when TrnxMnth='APR-11' and current_status='order shipped' and product_cat='ELECTRONICS - MOBILES' then MIS_Margin end)/100000,0) as [MARGIN_APR-11]
from dbo.COMMISSION_DB_APR10_APR11 where brand is not null group by brand --order by [GSV_APR-11] desc
)

Select [GSV_APR-11],[MARGIN_APR-11] from MyCTE where Srno <=10
union
Select Sum([GSV_APR-11]) , sum([MARGIN_APR-11]) from MyCTE where Srno > 10

No, you're never too old to Yak'n'Roll if you're too young to die.

ayadav0984
Starting Member

21 Posts

Posted - 2011-06-01 : 07:52:09

i am getting the error as :
Msg 207, Level 16, State 1, Line 1
Invalid column name 'GSV_APR-11'.
Since its defined later in the query. Please suggest how to move forward...

Bazalddo
Starting Member

22 Posts

Posted - 2011-06-01 : 07:57:38

Hi,

You could try something like this....

SELECT SUM(Col1)
FROM Tbl x
WHERE NOT EXISTS (SELECT TOP 10 *
FROM tbl y
WHERE x.PrimaryKey = y.PrimaryKey)

Hope this helps

Baz

Shahbaz Ahdi
Apps Developer
ODEON/UCI Cinema

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-01 : 08:14:31

quote:
Originally posted by ayadav0984

i am getting the error as :
Msg 207, Level 16, State 1, Line 1
Invalid column name 'GSV_APR-11'.
Since its defined later in the query. Please suggest how to move forward...

copy and paste the executed statement and the error here please so we can see what you are actually doing.

No, you're never too old to Yak'n'Roll if you're too young to die.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-06-01 : 08:37:23

Try this:

;With MyCTE as
(
Select brand,
isnull(sum(case when TrnxMnth='APR-11' and current_status='order shipped' and product_cat='ELECTRONICS - MOBILES' then suborderamount end)/100000,0) as [GSV_APR-11],
isnull(sum(case when TrnxMnth='APR-11' and current_status='order shipped' and product_cat='ELECTRONICS - MOBILES' then MIS_Margin end)/100000,0) as [MARGIN_APR-11]
from dbo.COMMISSION_DB_APR10_APR11 where brand is not null group by brand --order by [GSV_APR-11] desc
)
, MyCTE2 as
(
select Row_number() Over (Partition by brand order by [GSV_APR-11] desc) as Srno,
*
from
MyCTE
)

Select [GSV_APR-11],[MARGIN_APR-11] from MyCTE2 where Srno <=10
union
Select Sum([GSV_APR-11]) , sum([MARGIN_APR-11]) from MyCTE2 where Srno > 10

I have not carried out testing again and I feel that this can still be simplified. Just try.

ayadav0984
Starting Member

21 Posts

Posted - 2011-06-20 : 04:34:05

thanks for the reply but it didn't work...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-20 : 04:37:01

quote:
Originally posted by webfred

quote:
Originally posted by ayadav0984

i am getting the error as :
Msg 207, Level 16, State 1, Line 1
Invalid column name 'GSV_APR-11'.
Since its defined later in the query. Please suggest how to move forward...

copy and paste the executed statement and the error here please so we can see what you are actually doing.

No, you're never too old to Yak'n'Roll if you're too young to die.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources