count all >0 records - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

count all >0 records

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2007-01-24 : 12:30:01

count(foundedamount>0)
does not seem work, how can I count all record and fundedamount>0?

THX

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-24 : 12:39:36

select count(foundedamount)
from YourTable
where foundedamount > 0

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

jeff06
Posting Yak Master

166 Posts

Posted - 2007-01-24 : 12:42:01

Thanks
but I need to get count(foundedamount>0)/count(*) in the same query.
in access I can use count(iif(foundedamount>0, 1,0) but it does not work in sql sever.

Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-24 : 12:45:10

select count(*), sum(case when foundedamount > 0 then 1 else 0 end)
from YourTable

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

jeff06
Posting Yak Master

166 Posts

Posted - 2007-01-24 : 12:47:29

sum(case WHEN fundedamount >0 THEN 1 ELSE 0 END) as fcount

Thanks

jeff06
Posting Yak Master

166 Posts

Posted - 2007-01-24 : 12:52:47

One more question.
select count(*) as total, sum(case when foundedamount > 0 then 1 else 0 end)as part,
calculated part/calculeted toal as ratio

calculated part/calculeted toal as ratio does not work, how can I get the ratio?

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-24 : 14:30:01

You need to repeat the expressions, like this

select count(*) as total, sum(case when foundedamount > 0 then 1 else 0 end)as part,
sum(case when foundedamount > 0 then 1 else 0 end) / CAST(count(*) as decimal(9,2)) as ratio

You must cast one of the expressions as a non-integer type otherwise you will get integer division.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-24 : 16:06:34

Why not use a derived table?

select part, total, case when total = 0 then 0.0 else 1.0 * part / total end as ratio
from (
select count(*) as total, sum(case when foundedamount > 0 then 1 else 0 end) as part from table1
) as d

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources