Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Posted by Gabriel E
Gabriel E
8,916 Points

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

A X
A X
12,842 Points

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."

Gabriel E
Gabriel E
8,916 Points

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?

A X
A X
12,842 Points
A X
A X
12,842 Points

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.

Gabriel E
Gabriel E
8,916 Points

Oh, got it. Thanks for the help!

D Elis
D Elis
13,571 Points
D Elis
D Elis
13,571 Points

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')

Debora Ballabio
Debora Ballabio
1,030 Points
Debora Ballabio
Debora Ballabio
1,030 Points

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!?!?!?!?

Gabriel E
Gabriel E
8,916 Points
Gabriel E
Gabriel E
8,916 Points

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?

A X
A X
12,842 Points

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. :)