select top 10 and then all others

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)

select top 10 and then all others

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-19 : 12:50:17

Hi.
So what is the best practice to get a top 10 of values and then the sum of the rest?Is the a best practice?I would prefer a simple query but if i need a separate one then again how would i deduct the 10 first rows?
Thanks.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-19 : 13:22:30

I am trying this on a test table (id bigint, operationsid bigint) but i get a +10 difference!!


--- edo an valeis 12343123 einai gia na paei kato kato to total sum
SELECT id, sum(Sales) Sales
FROM (SELECT COALESCE(T2.id, 12343123) id, T1.Sales
        FROM (SELECT id, sum(operationsid) Sales
                FROM drugsoperations
              GROUP BY id
              
              ) T1
             
            left JOIN 
             (SELECT TOP 10 id, sum(operationsid) Sales
                FROM drugsoperations
              GROUP BY id) T2 
            on T1.id = T2.id
     ) T
GROUP BY T.id

So i get 2899 on the last line but if i do it by calculator i get 2889!
does it have to do by somehow adding 1 one, due to the top 10?!

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-19 : 13:53:35

I saw that the value is bigint. Can this be the cause?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources