return groupod by dates from subquery 1

[C_C_K]

Here's the logic I am atempting to apply

Have a base_orders table. It contains order_date and completed (shipped)

The first part is I needed to know the number of orders that were received and shipped out on the same day, for a range of days (a week for example)

Someone was nice enough to help me out with that last week, and now I need to extend it one more step

There's a separate table - base_order_shipments - that has ALL the shipment info. I'd like to add a column that shows ALL shipments for the same days within the date range. These would be orders, or partial orders that were NOT able to be shipped the day they were received, and I would expect the shipments from the outer join to be within those numbers as well - no need to exclude them). So generally the total shipments for any given day should be higher than the orders received and shipped on the same given day.

So the first part works (I've got the offending statements commented out here, when I un-comment those two lines, I get results across ALL time (for the past three years.)

I know this is going to be a subquery and that I have to join the grouped results of the inner join to the outer join, but I just cannot work out how to do it with my tables.

Any enlightenment will be greatly appreciated! Thanks for reading!

select
date(bo.order_date) as 'date',
count(bo.id) as 'orders received',
sum(date(bo.order_date) = date(bo.completed)) as 'shipped_complete'
#sum(date(bos.completed) = date(bo.completed)) as 'total shipments'

from base_orders bo
#join base_order_shipments bos on date(bos.completed) = date(bo.completed) 
where date(bo.order_date) between '2018-12-01' and '2018-12-13'


group by date(order_date)

 

[C_C_K]

I found an example online that I was able to understand and convert, and I just have one issue I cannot seem to overcome now....

there were two days where no shipments were made at all, 12-2 and 12-9, how can i get the orders received on those two days to appear, even though there were 0 shipments at all on those days... the having clause is what i tried, but the results are the same either way as pictured. If I do the subquery backwards (i.e subquery base_orders as opposed to order shipments) from the way it is below, I get all shipments for the entire period on the received and the total shipped row - I did try that.

Again, any help would greatly appreciated! thanks in advance!

SELECT 
    DATE(order_date),
    COUNT(bo.id) AS 'orders received',
    SUM(DATE(bo.order_date) = DATE(bo.completed)) AS 'shipped_complete',
    os.totalshipped
FROM
    (SELECT 
        DATE(completed) AS compdate, COUNT(id) AS 'totalshipped'
    FROM
        base_order_shipments
    WHERE
        DATE(completed) BETWEEN '2018-12-01' AND '2018-12-13'
    GROUP BY compdate) os
        LEFT JOIN
    base_orders bo ON DATE(order_date) = DATE(os.compdate)
WHERE
    DATE(bo.order_date) BETWEEN '2018-12-01' AND '2018-12-13'
GROUP BY DATE(bo.order_date)

having count(bo.id) >=0

 

[C_C_K]

I also tried to put a condition on that count tried 0 and null, but still no chg

FROM
    (SELECT 
        DATE(completed) AS compdate, [COLOR=#A40000]IF(COUNT(id) IS NULL,0,count(id))[/color] AS 'totalshipped'
    FROM
        base_order_shipments
    WHERE
        DATE(completed) BETWEEN '2018-12-01' AND '2018-12-13'
    GROUP BY compdate) os
        LEFT JOIN
    base_orders bo ON DATE(order_date) = DATE(os.compdate)

 

[feherke]

Hi

That [tt]join base_order_shipments bos on date[teal]([/teal]bos[teal].[/teal]completed[teal]) =[/teal] date[teal]([/teal]bo[teal].[/teal]completed[teal])[/teal][/tt] criteria will produce Cartesian product of all records with the same date. You should [tt]join[/tt] on the related id fields of those tables. Do you have something like base_order_shipments.base_order_id ?


Feherke.
feherke.github.io

 

[C_C_K]

Yes, there absolutely is... and it is the base_order_id. I was thinking I'd run into trouble because if there is no shipment there's no base_order_id entry in the table, but that is essentially the same problem I caused for myself using the date! Thank you again. I didn't put this in the original question you helped me out with before, because I didn't want to take advantage of your kindness. I'm sure this is it, and will try it a bit later. Thank you again.

 

[C_C_K]

I had such high hopes, but I'm still missing *something*

If I remove the comment indicator before the "and" in the JOIN I still get the same results.

It seems I have to keep the date in the join or I lose days, and the figures are not right. If I count(base_order_id) it's not available as a join field. It says it doesn't exist. Somehow I have to keep the dates in there, and I'm not sure how to incorporate joining on the base_order_id (note in bo id is the PK and the order_id - in BOS, the related field is base_order_id. id in BOS is that tables PK counting either field would produce the same results, even if there were multiple shipments of one order on the same day - which while unlikely could happen)

The only thing wrong with the previous solution is I'm missing entire days where there are zero shipments(even if there were orders received)


Any ideas?? As always, thanks in advance

SELECT 
    DATE(bo.order_date),
    COUNT(bo.id) AS 'orders received',
    SUM(DATE(bo.order_date) = DATE(bo.completed)) AS 'shipped_complete',
    os.totalshipped
FROM
    (SELECT 
        DATE(completed) AS 'compdate', COUNT(id) AS 'totalshipped', base_order_id as 'osid' 
    FROM
        base_order_shipments
    WHERE
        DATE(completed) BETWEEN '2018-12-01' AND '2018-12-13'
    GROUP BY date(completed)) os
        LEFT JOIN
    base_orders bo ON bo.id = os.osid  #and date(os.compdate) = date(bo.completed)   
WHERE
    DATE(bo.order_date) BETWEEN '2018-12-01' AND '2018-12-13'
GROUP BY DATE(bo.order_date)

 

[feherke]

Hi

Maybe I misunderstand your goal, but I don't get the reason to involve the 2[sup]nd[/sup] table.

Unfortunately there is no [tt]full join[/tt] in MySQL, for efficiency I would first get all involved days then join them with the counts :

[b]select[/b]
d[teal].[/teal]date[teal],[/teal]
o[teal].[/teal][i][green]`orders received`[/green][/i][teal],[/teal]
o[teal].[/teal][i][green]`shipped on same day`[/green][/i][teal],[/teal]
c[teal].[/teal][i][green]`orders completed`[/green][/i]

[b]from[/b] [teal]([/teal]
    [b]select distinct[/b]
    date[teal]([/teal]order_date[teal])[/teal] [b]as[/b] date

    [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]union[/b]

    [b]select distinct[/b]
    date[teal]([/teal]completed[teal])[/teal]

    [b]from[/b] orders

    [b]where[/b] completed [b]between[/b] [i][green]'2018-12-01'[/green][/i] [b]and[/b] [i][green]'2018-12-13'[/green][/i]
[teal])[/teal] d
[b]left join[/b] [teal]([/teal]
    [b]select[/b]
    date[teal]([/teal]order_date[teal])[/teal] [b]as[/b] date[teal],[/teal]
    count[teal]([/teal]id[teal])[/teal] [b]as[/b] [i][green]`orders received`[/green][/i][teal],[/teal]
    sum[teal]([/teal]order_date [teal]=[/teal] completed[teal])[/teal] [b]as[/b] [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]
[teal])[/teal] o [b]on[/b] o[teal].[/teal]date [teal]=[/teal] d[teal].[/teal]date
[b]left join[/b] [teal]([/teal]
    [b]select[/b]
    date[teal]([/teal]completed[teal])[/teal] [b]as[/b] date[teal],[/teal]
    count[teal]([/teal]id[teal])[/teal] [b]as[/b] [i][green]`orders completed`[/green][/i]

    [b]from[/b] orders

    [b]where[/b] completed [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]completed[teal])[/teal]
[teal])[/teal] c [b]on[/b] c[teal].[/teal]date [teal]=[/teal] d[teal].[/teal]date

[b]order by[/b] d[teal].[/teal]date

Alternatively since MySQL 8.0.1 you can use CTE.


Feherke.
feherke.github.io

 

[C_C_K]

Maybe I misunderstand your goal, but I don't get the reason to involve the 2nd table.

The second table, base_order_shipments shows ALL shipments for each day, not just those that were received and Fully shipped on that same given day.

For example, if an order was received outside of the range, and was unable to be shipped because the merch was out of stock... it could be shipped within that date range because the stock came in. The order would be in the base_orders table, but the created would be outside of that defined range. Also, it might take multiple shipments for the order/shipments to be complete, and it doesn't show the number of shipments that were required to make the order shipped in full.

So I have to bump it all up against the other table that shows ALL shipments for the day. That table doesn't really have info about an order being complete, just shipments being complete, if you get the distinction. I don't need to split the first two columns out of that last column though, I would expect them to be in the total.

The results from this post (17 Dec 18 21:43)are the closest, the only problem there is it's just missing days where there were ZERO shipments at all - no orders that were created that day were shipped, nor were any orders created outside of the range shipped.


so from the results from that post - On 12/1 88 orders were received dated 12/1. Only 5 of those orders were shipped complete, but there were 146 orders that were created outside of the 12/1 - 12/13 range that shipped.

It's really a shipment report as opposed to an order report.

If you think of a solution please share, but there would be no hard feelings if you don't. I'm just trying to say don't feel obligated - you've put a lot of effort into helping me and I do appreciate it, but at the end of the day it's not your problem and I'm sure you're read to move on to the next needy person! Thanks

 

[feherke]

Hi

Sorry, abit overloaded today. For now here is an explanation of what I understood you requested :

[blue]test>[/blue] [b]select[/b] [teal]*[/teal] [b]from[/b] orders [b]order by[/b] id[teal];[/teal]
+------+------------+------------+
| id   | order_date | completed  |
+------+------------+------------+
|    1 | 2018-11-01 | 2018-11-01 |
|    2 | 2018-11-02 | 2018-11-03 |
|    3 | 2018-11-03 | [highlight #ccf]2018-12-03[/highlight] |
|    4 | 2018-11-04 | 2018-12-14 |
|  [highlight #9f9]  5[/highlight] | [highlight #f99]2018-12-01[/highlight] | [highlight #99f]2018-12-01[/highlight] |
|  [highlight #9f9]  6[/highlight] | [highlight #f99]2018-12-01[/highlight] | [highlight #99f]2018-12-01[/highlight] |
|    7 | [highlight #f99]2018-12-01[/highlight] | [highlight #aaf]2018-12-02[/highlight] |
|    8 | [highlight #faa]2018-12-02[/highlight] | [highlight #bbf]2018-12-03[/highlight] |
|  [highlight #cfc]  9[/highlight] | [highlight #fcc]2018-12-04[/highlight] | [highlight #ccf]2018-12-04[/highlight] |
|   10 | [highlight #fdd]2018-12-05[/highlight] | 2018-12-15 |
|   11 | 2018-12-16 | 2018-12-16 |
|   12 | 2018-12-17 | 2018-12-18 |
+------+------------+------------+

[blue]test>[/blue] source c_c_k.sql
+------------+-----------------+---------------------+------------------+
| date       | orders received | shipped on same day | orders completed |
+------------+-----------------+---------------------+------------------+
| 2018-12-01 |             [highlight #f99]  3[/highlight] |                 [highlight #9f9]  2[/highlight] |              [highlight #99f]  2[/highlight] |
| 2018-12-02 |             [highlight #faa]  1[/highlight] |                   0 |              [highlight #aaf]  1[/highlight] |
| 2018-12-03 |            NULL |                NULL |              [highlight #bbf]  2[/highlight] |
| 2018-12-04 |             [highlight #fcc]  1[/highlight] |                 [highlight #cfc]  1[/highlight] |              [highlight #ccf]  1[/highlight] |
| 2018-12-05 |             [highlight #fdd]  1[/highlight] |                   0 |             NULL |
+------------+-----------------+---------------------+------------------+

Feherke.
feherke.github.io