Manipulating multiple count(*) queries

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

Transact-SQL (2000)

Manipulating multiple count(*) queries

Author

Topic

Ian11788
Starting Member

3 Posts

Posted - 2006-12-15 : 09:39:18

Hi,
I'm having problems manipulating the results from multiple count(*) queries.

I have a table 'RECOMMENDATIONS' holding a field called 'SCORE'. This field holds a number from 1 to 10 and has no null or zero figures.

I currently have the following three queries:
1) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)
2) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
3) SELECT COUNT(*) FROM RECOMMENDATIONS

I now need to combine these three queries so that i can divide and multiply the resulst: (query 2/query 1)*query 3

My initial idea was:
SELECT (COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)))* (SELECT COUNT(*) FROM RECOMMENDATIONS) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)

... but this gives a result of "0". I then stripped out the multiplication section to see if the divide was working:
SELECT COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)

... and again the result was "0"

Please could someone help me!!

Ian

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-15 : 10:10:06

I'd simplify this with variables, makes it easier to read.

Declare @Q1 decimal(10, 2), @Q2 decimal(10, 2), @Q3 decimal(10, 2)

Set @Q1 = (Select Count(*) From Recommendations Where Score In (1, 2, 3, 4, 5, 6))
Set @Q2 = (Select Count(*) From Recommendations Where Score In (9, 10))
Set @Q3 = (Select Count(*) From Recommendations)

If @Q1 <> 0
Select @Q2 / @Q1 * @Q3
Else
Print 'Divide by zero error'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-15 : 10:22:49

Something like this?

select 	
(sum(case when score in (1,2,3,4,5,6) then 1.0 else 0.0 end)/sum(case when score in (9,10) then 1.0 else 0.0 end))* count(*)
from RECOMMENDATIONS

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Ian11788
Starting Member

3 Posts

Posted - 2006-12-15 : 10:50:49

Thanks guys,
Both of these solutions work perfectly!

Ian

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-15 : 11:38:26

You need to multiply it by 1.0 to get correct values

select
(sum(case when score in (1,2,3,4,5,6) then 1.0 else 0.0 end)*1.0/sum(case when score in (9,10) then 1.0 else 0.0 end))* count(*)
from RECOMMENDATIONS

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources