Finding Percentage using OVER clause

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)

Finding Percentage using OVER clause

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-11-12 : 06:44:12

Hi Team,

I have the following schema. The first two queries work as expected. But the third query does not yield required result. How do I correct it to get percentage of each category?
(For the row corresponding to FundingID = 4, expected “thePercentage” value is 25. For other records the expected value s is 75)

DECLARE @Funding TABLE(FundingID INT,LKFundingType VARCHAR(50),StartDate DATETIME)
INSERT INTO @Funding (FundingID ,LKFundingType,StartDate ) VALUES (1,'NEW','1/1/2010')
INSERT INTO @Funding (FundingID ,LKFundingType,StartDate ) VALUES (2,'NEW','2/1/2010')
INSERT INTO @Funding (FundingID ,LKFundingType,StartDate ) VALUES (3,'NEW','1/5/2010')
INSERT INTO @Funding (FundingID ,LKFundingType,StartDate ) VALUES (4,'CHANGE','1/7/2010')

--List all fundings and total of each category
SELECT
FundingID,LKFundingType, COUNT(*) OVER(PARTITION BY LKFundingType) CategoryTotal
FROM @Funding

--List all fundings and the whole total
SELECT
FundingID,LKFundingType, COUNT(*) OVER() AS TOTALFundings
FROM @Funding

-- List all fundings and its category (INCORRECT query)
SELECT
FundingID,LKFundingType, ( COUNT(*) OVER(PARTITION BY LKFundingType) / COUNT(*) OVER() )* 100 AS thePercentage
FROM @Funding

Please advise.

Thanks
Lijo Cheeran Joseph

Sachin.Nand

2937 Posts

Posted - 2010-11-12 : 07:01:53

[code]
SELECT
FundingID,LKFundingType,
(COUNT(*) OVER(PARTITION BY LKFundingType)*1.0 /COUNT(*) OVER())*100 AS thePercentage
FROM @Funding
[/code]

Check the red part marked above.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-11-12 : 07:10:31

quote:
Originally posted by Lijo Cheeran Joseph

Interesting....

Could you please explain why it is necessary to multiply with 1.0 ?

Have a look here

sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx ·

PBUH

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-11-17 : 08:30:32

Thankss...

One more question.

I found somewhere that a query like folowing will give the percentage. But it does not. Can you please explain?

SELECT
LKFundingType, (COUNT(*) * 1.0) / COUNT(*) OVER() AS thePercentage
FROM @Funding
GROUP BY LKFundingType

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-17 : 12:20:50

It is becuase COUNT(*) OVER() gets you the total number of rows returned. Since you have applied an aggregate function COUNT(*) OVER() is resulting in 2, not 4 as it will without the aggregate function.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-18 : 05:56:07

SELECT
LKFundingType, (COUNT(*) * 100.0) / COUNT(*) OVER() AS thePercentage
FROM @Funding
GROUP BY LKFundingType

Madhivanan

Failing to plan is Planning to fail

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-11-18 : 06:47:37

Hi Madhivanan,
It is returning 50 and 150 rather than 25 and 75.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-18 : 10:20:00

Can you post some sample data from the table?

Madhivanan

Failing to plan is Planning to fail

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-11-18 : 10:31:28

quote:
Originally posted by madhivanan

Can you post some sample data from the table?

DECLARE @Funding TABLE(FundingID INT,LKFundingType VARCHAR(50),StartDate DATETIME)
INSERT INTO @Funding (FundingID ,LKFundingType,StartDate ) VALUES (1,'NEW','1/1/2010')
INSERT INTO @Funding (FundingID ,LKFundingType,StartDate ) VALUES (2,'NEW','2/1/2010')
INSERT INTO @Funding (FundingID ,LKFundingType,StartDate ) VALUES (3,'NEW','1/5/2010')
INSERT INTO @Funding (FundingID ,LKFundingType,StartDate ) VALUES (4,'CHANGE','1/7/2010')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-18 : 10:43:10

SELECT
LKFundingType, (COUNT(*) * 100.0) / (select COUNT(*) from @Funding) AS thePercentage
FROM @Funding
GROUP BY LKFundingType

Madhivanan

Failing to plan is Planning to fail

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-11-18 : 10:44:25

Thanks....

But the point of this question to avoid sub query using OVER()

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-18 : 10:46:50

quote:
Originally posted by Lijo Cheeran Joseph

Thanks....

But the point of this question to avoid sub query using OVER()

It can't be done becuase if you use count() over (), the count would be restricted to distinct value of column used in the group by clause and not all the values

Madhivanan

Failing to plan is Planning to fail

JohnHennesey
Starting Member

1 Post

Posted - 2010-12-03 : 17:00:18

Try this:
DECLARE @Funding TABLE(FundingID INT,LKFundingType VARCHAR(50),StartDate DATETIME)
INSERT INTO @Funding (FundingID ,LKFundingType,StartDate ) VALUES (1,'NEW','1/1/2010')
INSERT INTO @Funding (FundingID ,LKFundingType,StartDate ) VALUES (2,'NEW','2/1/2010')
INSERT INTO @Funding (FundingID ,LKFundingType,StartDate ) VALUES (3,'NEW','1/5/2010')
INSERT INTO @Funding (FundingID ,LKFundingType,StartDate ) VALUES (4,'CHANGE','1/7/2010')

select distinct lkfundingtype
,[PercentOfPopulation] = (COUNT(*) over (partition by lkfundingtype) * 1.0) / COUNT(*) over ()
from @Funding

---
John Hennesey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources