Databases Reporting with SQL Date and Time Functions Calculating Dates

SELECT COUNT(*) AS shipped_yesterday FROM orders WHERE status = "shipped" AND DATE("now", "-1 day")

This answer along with several other answers doesn't seem to work. Is there a bug here or am I missing some information?

1 Answer

Dave StSomeWhere
Dave StSomeWhere
19,809 Points

The challenge is asking for Count the total number of orders that were ordered yesterday and have the status of 'shipped'. Alias it to ordered_yesterday_and_shipped.

Your statement below has 2 errors - 1 - incorrect alias and 2 doesn't compare the DATE("now", "-1 day") to anything. Fix those 2 items and you should pass the challenge (just let me know if you need more info):

SELECT COUNT(*) AS shipped_yesterday FROM orders WHERE status = "shipped" AND DATE("now", "-1 day")

I still don't understand the necessary correction for error 2 (doesn't compare the DATE("now", "-1 day") to anything).

Dave StSomeWhere
Dave StSomeWhere
19,809 Points

Think about how SQL is deciding whether or not to include a row from the table in the result set, and you've been given the requirement to check 2 values - 1. total number of orders that were ordered yesterday and 2. orders that have the status of 'shipped' - so your where clause handles the status but doesn't handle the date - you did create an expression that properly identifies yesterday but you still need to compare that value to each row - does that help?