Databases Reporting with SQL Date and Time Functions Calculating Dates

Felix Belga
Felix Belga
4,267 Points
Posted by Felix Belga
Felix Belga
4,267 Points

I cannot see what is wrong with my code

In an ecommerce database there's an orders table with the columns id, product_id, user_id, address_id, ordered_on, status and cost. Count the total number of orders that were ordered yesterday and have the status of 'shipped'. Alias it to ordered_yesterday_and_shipped.

Emmanuel C
Emmanuel C
8,757 Points
Emmanuel C
Emmanuel C
8,757 Points

Can you post your code so we can help you better?

3 Answers

Emmanuel C
Emmanuel C
8,757 Points
Emmanuel C
Emmanuel C
8,757 Points

The -1 in the DATE function has to be in quotes and according to the video I add the string days to it so it looks like

DATE('now', '-1 days')

Felix Belga
Felix Belga
4,267 Points
Felix Belga
Felix Belga
4,267 Points

SELECT COUNT(status) AS ordered_yesterday_and_shipped FROM orders WHERE status = "shipped" AND ordered_on = DATE("now",-1);

Felix Belga
Felix Belga
4,267 Points
Felix Belga
Felix Belga
4,267 Points

that fixed it, thank you