Most effecient way to do a count AND percentage

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)

Most effecient way to do a count AND percentage

Author

Topic

mruprai
Starting Member

12 Posts

Posted - 2012-10-29 : 09:00:07

using sql serverhere is what i have

SELECT FilmParticipation.PartType, COUNT(*) FROM FilmParticipation
GROUP BY FilmParticipation.PartType;

however is there a more effcient way in SQL server 2008 using over?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-29 : 09:10:04

First open NewEditor in SSMS, then press ctrl+M (for getting execution plan along with query results)
-- Check this link
http://blog.sqlauthority.com/2007/08/28/sql-server-actual-execution-plan-vs-estimated-execution-plan/

Run following queries at a time:

SELECT PartType, COUNT(*) over(partition by PartType)
FROM FilmParticipation
GO

SELECT PartType, COUNT(*)
FROM FilmParticipation
GROUP BY PartType
GO

You can see the cost of the operation (2nd one will take less cost)

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 09:45:53

quote:
Originally posted by bandi

First open NewEditor in SSMS, then press ctrl+M (for getting execution plan along with query results)
-- Check this link
http://blog.sqlauthority.com/2007/08/28/sql-server-actual-execution-plan-vs-estimated-execution-plan/

Run following queries at a time:

SELECT PartType, COUNT(*) over(partition by PartType)
FROM FilmParticipation
GO

SELECT PartType, COUNT(*)
FROM FilmParticipation
GROUP BY PartType
GO

You can see the cost of the operation (2nd one will take less cost)

--
Chandu

trhe above two queries are not equivalent

first one gives details along with count value added to each of detail row whereas second query gives aggregated result alone

the equivalent query using OVER would be like


SELECT DISTINCT PartType, COUNT(*) over(partition  by PartType) 
FROM FilmParticipation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources