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
Stephen Funk
Courses Plus Student 2,650 PointsFor the BETWEEN objective test, using the BETWEEN statement ONLY errors. HELP
How do you do a COUNT statement for yesterday? The modification of the last objective from today to yesterday doesn't work. SELECT COUNT(status) AS shipped_yesterday FROM orders WHERE status = "shipped" AND ordered_on DATE("now", "- 1 day"); And the BETWEEN statement doesn't work. SELECT COUNT(status) AS shipped_yesterday FROM orders WHERE status = "shipped" AND ordered_on BETWEEN DATE("now") AND DATE("now", "- 1 day");
6 Answers
Stephen Funk
Courses Plus Student 2,650 PointsIn 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.
Nothing works. I tried both.
Steven Parker
231,271 PointsTry counting whole rows.
See if "COUNT(*)" helps. Also try reversing the order of the BETWEEN terms so that the earliest date comes first.
If neither of those resolve the issue, please provide a link to the course page you are working with to facilitate a more complete analysis.
Steven Parker
231,271 PointsNow that you added the instructions, it sounds like you don't need the BETWEEN. The challenge seems to only want the items that were sold yesterday and have a status of "shipped". Try that with counting whole rows. Rebekah has a good suggestion, check the syntax of the calculated date (but we know it's not MySQL).
To facilitate an even more accurate answer, provide a link to the course page itself.
Rebekah Smith
7,263 PointsWhen a query doesn't work, one troubleshooting approach is to try just the tricky parts. I would start with:
SELECT DATE("now", "- 1 day");
That isn't valid in MySQL, but idk what language you're working with. Correct syntax in MySQL varies, one way to do it is:
SELECT SUBDATE(NOW(), 1);
Stephen Funk
Courses Plus Student 2,650 PointsI fixed it. It was a space between the dash and 1 --> -1 day. But I have more questions I'll post for other stuff.
Steven Parker
231,271 PointsGood catch. 
I would have needed the link to the challenge page to get that one.
And you didn't need BETWEEN either, right?
Stephen Funk
Courses Plus Student 2,650 Pointsno I did not - added a modifier "-1 day" and the space between the dash and the 1 got me in the pre-programmed quiz objective where they only accept one specific way. But I have another question posted about modifying dates for all columns without using the STRFTIME function.
Nivedita Tiwari
2,313 Pointslooking fro an answer on the same
Nivedita Tiwari
2,313 Pointsi finally got the answer for the same issue. I was missing the = operator between ordered_on and DATE("now", "-1 day").
Nivedita Tiwari
2,313 PointsNivedita Tiwari
2,313 PointsHi Stephan, I am stuck in the same query an cant get it right.