Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-16-2017
07:49 AM

04-16-2017
07:49 AM

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.

Many thanks in advance for your advice,

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;

Solved! Go to Solution.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-17-2017
11:46 AM

04-17-2017
11:46 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-16-2017
07:59 AM

04-16-2017
07:59 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-16-2017
08:05 AM

04-16-2017
08:05 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-16-2017
08:36 AM

04-16-2017
08:36 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-16-2017
08:54 AM

04-16-2017
08:54 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-16-2017
08:56 AM

04-16-2017
08:56 AM

Pardon *stype = purchase

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-16-2017
09:05 AM

04-16-2017
09:05 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-16-2017
09:24 AM

04-16-2017
09:24 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-17-2017
04:10 AM

04-17-2017
04:10 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-17-2017
11:46 AM

04-17-2017
11:46 AM

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