SQL count records with 0 Value

Question

I'm using a SQL query for an app, but I cant get it to count values with 0 results.

The code is shown below:

select CarrierPosition, COUNT(*) as FailCount from tbl_0000000024, tbl_0000000025 where asictestdate >= DATEADD(hour,-6, getdate()) and FK_AS0000000032 = fk_as0000000056 and fk_as0000000046 = '135' and FailReason = '151' and tbl_0000000025.Status = ' Current' group by CarrierPosition Order by CarrierPosition

It should show up a fail count for 8 positions, but leaves out positions 1 and 4 as they have a FailCount of 0. How do I get it to show the other 2 rows?

Thanks

jarlh · Accepted Answer · 2015-11-25 14:06:52Z

2

Do a LEFT JOIN, modern explicit syntax, to get those rows too.

select CarrierPosition, COUNT(*) as FailCount from tbl_0000000024 LEFT JOIN tbl_0000000025 ON FK_AS0000000032 = fk_as0000000056 where asictestdate >= DATEADD(hour,-6, getdate()) and fk_as0000000046 = '135' and FailReason = '151' and tbl_0000000025.Status = ' Current' group by CarrierPosition Order by CarrierPosition

(Perhaps it should be the other way, the column and table names don't describe very well. Switch LEFT to RIGHT and see what happens if the above query doesn't work as expected.)

edited Nov 25, 2015 at 14:06

answered Nov 25, 2015 at 14:04

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aaron Over a year ago

For extra explanations see this venn diagrams of sql joins : codeproject.com/KB/database/Visual_SQL_Joins/…

Doc Over a year ago

You should add some kind of explanation to your answer... you know... for SQL newbies :)

SP Mully Over a year ago

Thanks for your help. FK_AS0000000056 is on tbl_25 and FK_AS0000000032 is on tbl_24 - both are the part ID. I'd been playing about with Joins earlier but I think I had syntax wrong. This one will execute but gives the same responses, even with left and right swapped.

SP Mully Over a year ago

Reposting in case you missed my edit (I'm new to this) Thanks for your help. FK_AS0000000056 is on tbl_25 and FK_AS0000000032 is on tbl_24 - both are the part ID. I'd been playing about with Joins earlier but I think I had syntax wrong. This one will execute but gives the same responses, even with left and right swapped

Collectives™ on Stack Overflow

SQL count records with 0 Value

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related