Need to count any instance where value of field is zero 1

[laina222]

Hello all,
I am writing a query and am stuck on a certain field I need to create. I want the field to count the instances of where a specific field in my table = 0.

 

[PHV]

Any chance you could be more specific ?
ie, what you have so far, some input samples, expected result, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[laina222]

Sure thing...

Field: CurrentField
Value: 2
1
3
0
4
0
2

NewField: 2
(Because there are 2 instances of the value "0" in the CurrentField)

Does that make sense? I'm sure I'm explaining this in a far more elaborate manner than I need to, but that's just me!

 

[laina222]

Oh yeah, one more thing. I don't want to include the Null values, which there could be plenty of. Should have put that in there...

 

[PHV]

SELECT Count(*) FROM yourTable WHERE CurrentField=0;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Oops, sorry, hit submit to fast:
SELECT Count(*) As NewField FROM yourTable WHERE CurrentField=0;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[laina222]

Okay, but what if I had more than one field that I wanted this for? The WHERE statement wouldn't work, would it?

 

[laina222]

Oh never mind! It's too early...thanks!