CODEWITHSUNDEEP
sql
Aidan Cary
Aidan Cary

Reputation: 23

Conditionals weirdness

I am running a query that effectively looks like

SELECT SUM(CASE WHEN name LIKE '%ad%' AND x > 0 THEN 1 ELSE 0 END) as num_x,
       SUM(CASE WHEN name LIKE '%ad%' AND y > 0 THEN 1 ELSE 0 END) as num_y,
       SUM(CASE WHEN name LIKE '%ad%' AND z > 0  AND Z <= 100 THEN 1 ELSE 0 END) as num_z,
       SUM(CASE WHEN name LIKE '%ad%' AND x > 0 OR y > 0 OR z > 0  AND Z <= 100 THEN 1 ELSE 0 END) as distinct_streams
FROM table

The purpose of the query is to find the number of ad playbacks that meet different error conditions. I also want to find the distinct number of erroneous streams as some of the conditions could happen during the same stream. The problem is that the above query returns a larger number of distinct_streams then the 3 above combined. Do you see anything in that could be causing this?

Upvotes: 2

Views: 41

Answers (2)

LukStorms
LukStorms

Reputation: 29667

In SQL all AND are evaluated before all OR.

So a criteria like: 

name LIKE '%ad%' AND x > 0 OR y > 0 OR z > 0 AND z <= 100

Is actually evaluated as:

(name LIKE '%ad%' AND x > 0) OR y > 0 OR (z > 0 AND z <= 100)

While you probably expected:

name LIKE '%ad%' AND (x > 0 OR y > 0 OR (z > 0 AND z <= 100))

So try this:

SELECT 
COUNT(CASE WHEN x > 0 THEN 1 END) AS num_x,
COUNT(CASE WHEN y > 0 THEN 1 END) AS num_y,
COUNT(CASE WHEN z > 0  AND z <= 100 THEN 1 END) AS num_z,
COUNT(CASE WHEN x > 0 OR y > 0 OR (z > 0 AND z <= 100) THEN 1 END) AS distinct_streams
FROM "table" AS t
WHERE name LIKE '%ad%';

Basically, when using both AND and OR it's often safer to include parentheses. Just to avoid misunderstandings with the logic.

Upvotes: 2

Mureinik
Mureinik

Reputation: 311778

You're missing the fact that the logical and has a higher precedence than the logical or. Surround the or sequence with parenthesis and you should be OK:

SUM(CASE WHEN (name LIKE "%ad% AND (x > 0 OR y > 0 OR z > 0 AND Z <= 100) 
         THEN 1 
         ELSE 0
    END) as distinct_streams

Upvotes: 0

Related Questions