Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Databases

Count was wrong, expected 14. Not sure how to manipulate this.

So, here is the link to the challenge: https://teamtreehouse.com/library/reporting-with-sql/date-and-time-functions/calculating-dates-2. Just not at all sure what to do. Any advice would help. Thanks, Gabriel

5 Answers

Hey Gabriel, I already talked about and answered this question here: https://teamtreehouse.com/community/what-did-i-do-wrong-27 but here's the code here:

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

And as far as explaining it:

-SELECT COUNT(status) AS shipped_yesterday ... we're counting all the rows in status and labeling it shipped_yesterday

-FROM orders ... we're getting this information from the orders table

-WHERE status = "shipped" ... this restricts the results of our SELECT down to just the shipped items in the column status

-AND ordered_on = DATE("now", "-1 day"); ... we also want to restrict the results to items that were, in addition to shipped, ordered on the date of now and yesterday. The now is confusing because we just kinda have to pretend that the shipments happened today in our imaginary database :) Also don't forget the semi-colon acts like a period in a sentence so the SQL reading program knows to end the query there.

If you want to understand further, the SQL program will "read" this program from the top down, so all it knows at first is we want to select COUNT(status) from something and we want it labeled shipped_yesterday and it works its way down the code until it finds out, "Ah! The human wants us to find all the orders that were shipped today and yesterday and display the results of that...Cool, I'll do that."

Ok thanks! When I looked under the category before writing my question of "similar questions" I didn't see anything about it. Is there anyway, like you mentioned, to ensure I'm not asking a question that's already been asked and answered?

I'll Google it actually, which I know is an extra step. So I normally just copy and paste the whole challenge question or what they want you to get. So in your case I look up: "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 have the status of shipped yesterday. Alias it to shipped_yesterday." And the top 3 results on Google (as of when I write this) are from people asking about this on Treehouse.

Oh, got it. Thanks for the help!

Here is the code I used that worked:

SELECT COUNT(*) AS ordered_yesterday_and_shipped FROM orders WHERE status ='shipped' AND ordered_on = DATE('now', '-1 day')

Hi, I tried the code above and it didn't work. Is this a bug? Got stuck in the same place months ago, went all the way back to the beginning and here I am again. Stuck in the same place. SO FRUSTRATING!!!! Andrew Chalkley can you help me so I can move on?

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.

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

Bummer: Your count calculation was wrong. Was expecting the count of 14. WTF!?!?!?!?

Sorry, I do have one last question. I'm really having a hard time with PHP Build a simple website. Would it be best to go through the PHP basics course first?

Sorry Gabriel, I haven't gone through the PHP Build A Website myself yet, so I can't help with that one! But I'd always recommend going through a bit of the basics to at least get a general idea of how to use a new language before trying to use it...some people learn differently than that, but that's what makes sense for my brain. :)