Analytics
Enthusiast

## Why does <> in my query not work?

Hello,

Could someone please help me understand why my WHERE statement is not working? My query below works if I use a case statement,

but I don't understand WHY the parenthesis does not combine to one logical operation and is instead seen as two seperate ones.

- Why does not (Year_num <> 2005 AND Month_num<> 8) not get seen as one combination ?

Christine

```SELECT CASE WHEN (s.msa_high >= 50 AND s.msa_high <= 60)  THEN 'low'
WHEN (s.msa_high > 60 AND s.msa_high <= 70)  THEN 'medium'
WHEN  s.msa_high >70 THEN 'high'
END AS EduCat, SUM(CleanedData.SalesDays) AS TotSalesDays, SUM(CleanedData.Revenue) AS TotRev
FROM (SELECT EXTRACT(YEAR from saledate) AS Year_num, EXTRACT(MONTH from saledate) AS Month_num, store AS Store, COUNT(DISTINCT saledate) AS SalesDays,
SUM(AMT) AS Revenue, Revenue/SalesDays AS Avg_Daily_Revenue
FROM trnsact
WHERE stype = 'P' AND (Year_num <> 2005 AND Month_num<> 8)
GROUP BY Year_num, Month_num, Store
HAVING SalesDays >= 20 ) AS CleanedData
INNER JOIN store_msa s ON s.store = CleanedData.store
GROUP BY EduCat;```

Accepted Solutions

## Re: Why does <> in my query not work?

Christine,

This is happenigns due to De Morgan's law of boolean logic / set theory (https://en.wikipedia.org/wiki/De_Morgan%27s_laws). Quickly, when you "distribute" a NOT your ANDs switch to ORs.

Your case statement is saying what you want: I want all rows that are not in August of 2005

With logical opperators we can write: WHERE NOT (year_num = 2005 AND month_num = 8)

To distribute the NOT with logical opperators we end up with: WHERE (year_num <> 2005 OR month_num <> 8)

This is confusing to us becase we are looking for rows that are not something and also not something else so we want to use an AND, but check out the Ven Diagrams in the De Morgan's Law link and see if it makes sence why we want OR instead.

Good luck!

Michelle

1 ACCEPTED SOLUTION
10 REPLIES

## Re: Why does <> in my query not work?

Please explain what "doesn't work" means. And please provide the case expression used instead that gets the desired result.

Enthusiast

## Re: Why does <> in my query not work?

Hi Todd,

Thank you. I mean that it is not giving me the desired result which is the one below.

```SELECT CASE WHEN (s.msa_high > 50 AND s.msa_high <= 60)  THEN 'low'
WHEN (s.msa_high > 60 AND s.msa_high <= 70)  THEN 'medium'
WHEN  s.msa_high >70 THEN 'high'
END AS EduCat, SUM(CleanedData.SalesDays) AS TotSalesDays, SUM(CleanedData.Revenue) AS TotRev
FROM (SELECT EXTRACT(YEAR from saledate) AS Year_num, EXTRACT(MONTH from saledate) AS Month_num, store AS Store, COUNT(DISTINCT saledate) AS SalesDays,
SUM(AMT) AS Revenue, Revenue/SalesDays AS Avg_Daily_Revenue, (CASE
WHEN Year_num= '2005' AND Month_num= '8' THEN 'exclude' ELSE 'include' END) AS Filter FROM trnsact WHERE stype='p' AND Filter='include' GROUP BY store, Year_num, Month_num HAVING SalesDays >= 20 )AS CleanedData
INNER JOIN store_msa s ON s.store = CleanedData.store
GROUP BY EduCat;```

## Re: Why does <> in my query not work?

What are the data types and data representation in those types? In the case expression character compares are being done. In the <> expressions numeric compares are being done.

Enthusiast

## Re: Why does <> in my query not work?

Hello,

I want to exclude the year 2005 and month of August from sales. Stypye =p( purchase.)

Please see a screenshot of the results obtained.

Without case clause and <> (wrong result)

With case clause (right result)

Thanks Todd

Highlighted
Enthusiast

## Re: Why does <> in my query not work?

Pardon *stype = purchase

## Re: Why does <> in my query not work?

Perhaps I was not clear enough with my question:

WHEN Year_num= '2005' AND Month_num= '8'  is performing character string comparisons due to the numbers being placed inside quotes.

AND (Year_num <> 2005 AND Month_num<> 8) is performing numeric comparisons - no quotes.

Implicit data type casting has to be done when they types do not match.

What data types are Year_num and Month_num? How is the data stored in those columns?

Enthusiast

## Re: Why does <> in my query not work?

Ahh I am sorry, I am new to SQL so I did not correctly understand. Please bear with me, its this what your looking for?

Enthusiast

## Re: Why does <> in my query not work?

Hello Todd,

Could you please kindly clarify what you need from me? I want to learn why my query is not giving me the right result.

## Re: Why does <> in my query not work?

Christine,

This is happenigns due to De Morgan's law of boolean logic / set theory (https://en.wikipedia.org/wiki/De_Morgan%27s_laws). Quickly, when you "distribute" a NOT your ANDs switch to ORs.

Your case statement is saying what you want: I want all rows that are not in August of 2005

With logical opperators we can write: WHERE NOT (year_num = 2005 AND month_num = 8)

To distribute the NOT with logical opperators we end up with: WHERE (year_num <> 2005 OR month_num <> 8)

This is confusing to us becase we are looking for rows that are not something and also not something else so we want to use an AND, but check out the Ven Diagrams in the De Morgan's Law link and see if it makes sence why we want OR instead.

Good luck!

Michelle