subqueries with multiple date results

[C_C_K]

subqueries with multiple date results

I am building a query that shows the number of orders received, on each date within a range of dates

Wanted: a 2nd column of orders that were shipped in full on the same day they were received.



this is what I have so far, and the results of orders in are fine, but for the shipment column, i'm getting the same total for each day, the number of shipments encompassed by the the entire range. I know I need to somehow join the subquery to the main query, but I just can't work it out.

I tried it with an if in relation to the completed date, without using a subquery, but because I HAVE to have the "does not meet condition" branch of the IF, which i tried to make zero, I was not getting reliable results.

If there is a better way than using a subquery, I'm open to it.

Thanks in Advance!

SELECT 
    DATE(order_date) AS 'date',
    COUNT(o.id),
    (SELECT 
            COUNT(id)
        FROM
            orders #edited table name from base_orders to orders
        WHERE
            DATE(completed) BETWEEN '2018-12-01' AND '2018-12-13') AS 'shipped_complete'
FROM
    orders o
WHERE
    DATE(o.order_date) BETWEEN '2018-12-01' AND '2018-12-13'
GROUP BY DATE(o.order_date)

snip of results

2018-12-01 88 1640
2018-12-02 91 1640
2018-12-03 99 1640
2018-12-04 116 1640
2018-12-05 96 1640

Any help would be greatly appreciated. I'm sure I'm trying to make it be harder than it is!

 

[feherke]

Hi

What is the relation between orders and base_orders ?


Feherke.
feherke.github.io

 

[C_C_K]

oh I was trying to simplify the table name... base orders IS orders in this case - i just missed it

 

[feherke]

Hi

Ah, then I think this will be enough :

[b]select[/b]
date[teal]([/teal]order_date[teal])[/teal] [b]as[/b] [i][green]`date`[/green][/i][teal],[/teal]
count[teal]([/teal]id[teal])[/teal] [b]as[/b] [i][green]`orders received`[/green][/i][teal],[/teal]
sum[teal]([/teal]date[teal]([/teal]order_date[teal])[/teal] [teal]=[/teal] date[teal]([/teal]completed[teal]))[/teal] [i][green]`shipped on same day`[/green][/i]

[b]from[/b] orders

[b]where[/b] order_date [b]between[/b] [i][green]'2018-12-01'[/green][/i] [b]and[/b] [i][green]'2018-12-13'[/green][/i]

[b]group by[/b] date[teal]([/teal]order_date[teal])[/teal]

Feherke.
feherke.github.io

 

[C_C_K]

I just knew I was making it harder than it had to be.

I had to tweak that a tiny bit for mysql, but that's definitely got it. THANK YOU!

Now I'm off to add a "shipments" table to get the grand total of shipments for each day, regardless of the order date, which I know is going to be a subquery. So I may be back!




Just for giggles, this is the final version.

select
date(order_date) as 'date',
count(id) as 'orders received',
sum(date(order_date) = date(completed)) as 'shipped_complete'

from orders

where order_date between '2018-12-01' and '2018-12-13'

group by date(order_date)