×
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

subqueries with multiple date results

subqueries with multiple date results

subqueries with multiple date results

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


CODE --> mysql

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!

RE: subqueries with multiple date results

Hi

What is the relation between orders and base_orders ?

Feherke.
feherke.github.io

RE: subqueries with multiple date results

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

RE: subqueries with multiple date results

Hi

Ah, then I think this will be enough :

CODE --> MySQL

select
date(order_date) as `date`,
count(id) as `orders received`,
sum(date(order_date) = date(completed)) `shipped on same day`

from orders

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

group by date(order_date) 


Feherke.
feherke.github.io

RE: subqueries with multiple date results

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

CODE --> MySQL

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) 

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