MYSQL Count first

[coding_noobie]

How do I get a count of the number of people who’s first order was for exactly $20?

I’ve tried using:

SELECT count(Distinct customer_id),order_total FROM orders group by order_total;

but that gives me a number that I know it’s right. It doesn’t matter how many other orders they’ve placed I just need to know how many people’s first order was for $20

[r937]

this is a really good question – so much so, that i’m pretty sure it’s a homework question

if it isn’t, my apologies, but you’ll have to work it out from the following clues

to get each customer’s first order, you have to do a GROUP BY on customer (since you’re interested in all of them, not a particluar one), and a MIN(orderdate) to get the first order for each customer

so that’s a really simple query, and it produces a result consisting of a customer column and a date column

now, you have to join these results back to the orders – so your FROM clause will contain the simple query we just talked about, as a subquery or “derived table” (google this for more info), which is treated just like a table, and you join this with INNER JOIN to the orders table, joining on two columns – customer and date

then you add a WHERE clause for the $20, put COUNT(*) into the SELECT clause, and vwalah, bob’s your uncle