×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

return groupod by dates from subquery

return groupod by dates from subquery

return groupod by dates from subquery

(OP)
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!

CODE --> MySQL

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) 


RE: return groupod by dates from subquery

(OP)
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!

CODE --> MySQL

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 

RE: return groupod by dates from subquery

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


CODE --> MySQL

FROM
    (SELECT 
        DATE(completed) AS compdate, IF(COUNT(id) IS NULL,0,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) 

RE: return groupod by dates from subquery

Hi

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

Feherke.
feherke.github.io

RE: return groupod by dates from subquery

(OP)
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.

RE: return groupod by dates from subquery

(OP)
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


CODE --> MySQL

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) 

RE: return groupod by dates from subquery

Hi

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

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

CODE --> MySQL

select
d.date,
o.`orders received`,
o.`shipped on same day`,
c.`orders completed`

from (
    select distinct
    date(order_date) as date

    from orders

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

    union

    select distinct
    date(completed)

    from orders

    where completed between '2018-12-01' and '2018-12-13'
) d
left join (
    select
    date(order_date) as date,
    count(id) as `orders received`,
    sum(order_date = completed) as `shipped on same day`

    from orders

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

    group by date(order_date)
) o on o.date = d.date
left join (
    select
    date(completed) as date,
    count(id) as `orders completed`

    from orders

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

    group by date(completed)
) c on c.date = d.date

order by d.date 
Alternatively since MySQL 8.0.1 you can use CTE.

Feherke.
feherke.github.io

RE: return groupod by dates from subquery

(OP)

Quote (Feherke)

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 :)


RE: return groupod by dates from subquery

Hi

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

CODE

test> select * from orders order by id;
+------+------------+------------+
| id   | order_date | completed  |
+------+------------+------------+
|    1 | 2018-11-01 | 2018-11-01 |
|    2 | 2018-11-02 | 2018-11-03 |
|    3 | 2018-11-03 | 2018-12-03 |
|    4 | 2018-11-04 | 2018-12-14 |
|    5 | 2018-12-01 | 2018-12-01 |
|    6 | 2018-12-01 | 2018-12-01 |
|    7 | 2018-12-01 | 2018-12-02 |
|    8 | 2018-12-02 | 2018-12-03 |
|    9 | 2018-12-04 | 2018-12-04 |
|   10 | 2018-12-05 | 2018-12-15 |
|   11 | 2018-12-16 | 2018-12-16 |
|   12 | 2018-12-17 | 2018-12-18 |
+------+------------+------------+

test> source c_c_k.sql
+------------+-----------------+---------------------+------------------+
| date       | orders received | shipped on same day | orders completed |
+------------+-----------------+---------------------+------------------+
| 2018-12-01 |               3 |                   2 |                2 |
| 2018-12-02 |               1 |                   0 |                1 |
| 2018-12-03 |            NULL |                NULL |                2 |
| 2018-12-04 |               1 |                   1 |                1 |
| 2018-12-05 |               1 |                   0 |             NULL |
+------------+-----------------+---------------------+------------------+ 

Feherke.
feherke.github.io

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close