sql query count

I have a query that is counting all the rows
It shouldnt
SELECT COUNT(SurveyUID) FROM SS_Surveys 
WHERE JobID = 10299 
AND SurveyStatus = 1 
AND Inclination is not null 
OR TotalGravityField IS NOT NULL 
OR HoursLeft IS NOT NULL 
OR ToolTemp IS NOT NULL

Open in new window

the data is
SurveyStatus |  JobID   |  Inclination   |   TotalGravityField    |   HoursLeft   |    TooolTemp
1                          10299              1.3                        NULL                        NULL                  NULL          
- --------------------------------------------------------------------------------------------------------------------------------
1                           1235               1.56                               2.3                       3                     12
----------------------------------------------------------------------------------------------------------------------------------
1                           1245               25.5                           2.3                                 23               7
----------------------------------------------------------------------------------------------------------------------------------
1                           10299              16                             2.5                             23                    7.0


This query should return 1 for the last row in the data example can anyone help
LVL 6
r3nderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leo TorresSQL DeveloperCommented:
Try
[code]
SELECT COUNT(1) FROM SS_Surveys
WHERE JobID = 10299
AND SurveyStatus = 1
AND Inclination is not null
OR TotalGravityField IS NOT NULL
OR HoursLeft IS NOT NULL
OR ToolTemp IS NOT NULL

[\code]
0
r3nderAuthor Commented:
Sorry Leo the Count is 4
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
SELECT COUNT(*) FROM SS_Surveys 
WHERE JobID = 10299 
AND SurveyStatus = 1 
AND (Inclination is not null 
  OR TotalGravityField IS NOT NULL 
  OR HoursLeft IS NOT NULL 
  OR ToolTemp IS NOT NULL)

Open in new window

AND has precedence over OR, so the ANDs are evaluated, and then the ORs:
   (SurveyStatus =1 AND Incliniation is not null)
   or ...
Since all rows have data in at least one column, all are returned.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

r3nderAuthor Commented:
Thank you Olemo its close and it struck me that maybe the ors should be in () so that they are evaluated first but the problem is it returns 1 - if there are nulls in any of those 4 fields I don't want it counted
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
1 is the expected result for that JobID. isn't it?
0
r3nderAuthor Commented:
sorry my fault it returns 2
0
Scott PletcherSenior DBACommented:
SELECT COUNT(SurveyUID)
FROM SS_Surveys
WHERE JobID = 10299
AND SurveyStatus = 1
AND COALESCE(HoursLeft, Inclination, ToolTemp, TotalGravityField) IS NOT NULL

Out of habit, I re-arranged the entries in the COALESCE in ascending order, as I always do for that an IN -- naturally undo that if you prefer them in the other order :-).
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Now I'm getting it. You need all columns to contain data. In that case replace all your ORs with ANDs.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
r3nderAuthor Commented:
I guess it should be
SELECT COUNT(*) FROM SS_Surveys 
 WHERE JobID = 10299 
 AND SurveyStatus = 1 
 AND Inclination is not null 
 AND TotalGravityField IS NOT NULL 
 AND HoursLeft IS NOT NULL 
 AND ToolTemp IS NOT NULL

Open in new window

0
r3nderAuthor Commented:
Thanks QLEMO
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.