asked on

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

Leo Torres

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]

r3nder

ASKER

Sorry Leo the Count is 4

Qlemo

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.

r3nder

ASKER

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

Qlemo

1 is the expected result for that JobID. isn't it?

r3nder

ASKER

sorry my fault it returns 2

Scott Pletcher

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 :-).

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

r3nder

ASKER

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

r3nder

ASKER

Thanks QLEMO