COUNT with condition [RESOLVED]

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)

COUNT with condition [RESOLVED]

Author

Topic

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-05-30 : 16:29:12

I am joining 2 tables, one is my article master table, the other one is my article stock table. I would like to count the amount of article numbers I haveas well as the amount of article numbers with pieces in stock. Can I combine this in one statement?


select COUNT(a.artNo), COUNT(artNo --with inventory)
from articles a
inner join atriclestock b on a.artNo = b.artNo
where a.artStatus = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 16:31:23

you can


select COUNT(a.artNo) as totalcnt, COUNT(b.artNo) as instockcnt
from articles a
left join atriclestock b on a.artNo = b.artNo
where a.artStatus = 1

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

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-05-30 : 17:00:58

Thank you, that works with a little modification because my articlestock table also contains artNo records where the inventory is 0.


select COUNT(a.artNo) as totalcnt, COUNT(b.artNo) as instockcnt
from articles a
left join 
(select artNo from  atriclestock  where inStock > 0) b 
on a.artNo = b.artNo
where a.artStatus = 1

When I'm trying to divide my article count by my articlestock count I get a value of 0, but I would expect a value of 0.XX.
Can I not divide one count by another?


select COUNT(a.artNo) as totalcnt, COUNT(b.artNo) as instockcnt
, COUNT(b.artNo)/ COUNT(a.artNo) as ratio
from articles a
left join 
(select artNo from  atriclestock  where inStock > 0) b 
on a.artNo = b.artNo
where a.artStatus = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 17:13:21

you will get value once you change it to decimal datatype. see


select COUNT(a.artNo) as totalcnt, COUNT(b.artNo) as instockcnt
, COUNT(b.artNo)*1.0/ COUNT(a.artNo) as ratio
from articles a
left join 
(select artNo from  atriclestock  where inStock > 0) b 
on a.artNo = b.artNo
where a.artStatus = 1

the reason was COUNT returns only int so result will be converted to int by dropping the decimal part making it 0

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 17:14:03

see

http://beyondrelational.com/modules/2/blogs/70/posts/10825/beware-of-implicit-conversions.aspx

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

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-05-30 : 17:15:27

Great - thank you very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 17:17:51

welcome!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources