mysql - Historical inventory by day -

mysql - Historical inventory by day -

i using mysql , have table log inventory orders. table (a simplification) looks this:

||id|ordered_date |date |itemid| status || ||1 |jan-4 |jan-4 | | ordered || ||2 |jan-6 |jan-6 | b | ordered || ||3 |jan-8 |jan-8 | c | ordered || ||4 |jan-8 |jan-9 | c | shipped || ||4 |jan-6 |jan-9 | b | shipped || ||4 |jan-4 |jan-10| | shipped ||

what want total number of orders per day have not yet shipped, this:

date | inventory jan-4 | 1 jan-5 | 1 jan-6 | 2 jan-7 | 2 jan-8 | 3 jan-9 | 1 jan-10| 0

i can find total number of unshipped orders on single day using query this:

select count(*) inventory status = "shipped" , date > "jan-8" , ordered_date <= "jan-8"

but need number of orders days. , above query has problem in won't find orders have not shipped yet. so, help appreciated.

(assume date column valid sql date)

try this:

select date, count(*) inventory status != 'shipped' grouping date order date;

mysql sql