Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I'm not yet good at MySQL. Please check my sql below and help me understand where I went wrong with it. All I need is just one record for the order.id and the returned record must be the one whose shipped date is the latest.

Database error: Invalid SQL: SELECT orders.id, orders.customer_fk FROM orders INNER JOIN order_details ON order_details.order_fk=orders.id WHERE orders.payment_method IN ('AS','AC') AND ((orders.order_status='SHP' AND order_details.item_status='SHP' AND MAX(order_details.shipped_date) <= '2021-08-07') OR (orders.order_status='CAN' AND orders.order_date <= '2021-08-07 09:56:18')) AND orders.pii_status <> '1'GROUP BY orders.id
MySQL Error: 1111 (Invalid use of group function)
Instead of using MAX alone try to use a subselect
If you don't want the mad for every order.id then you need to add a inner join
SELECT 
    orders.id, orders.customer_fk
    orders
        INNER JOIN
    order_details ON order_details.order_fk = orders.id
WHERE
    orders.payment_method IN ('AS' , 'AC')
        AND ((orders.order_status = 'SHP'
        AND order_details.item_status = 'SHP'
        AND (SELECT MAX(shipped_date) FROM order_details WHERE order_fk = orders.id) <= '2021-08-07')
        OR (orders.order_status = 'CAN'
        AND orders.order_date <= '2021-08-07 09:56:18'))
        AND orders.pii_status <> '1'
GROUP BY orders.id
To explain it somewhat further
SELECT MAX(shipped_date) FROM order_details WHERE order_fk = orders.id) <= '2021-08-07'
Return true or false for every Order.id as it checks for every row in the outer select what the maximum date is and then checks it against the date.
After selecting all rows you GROUP BY(which i still don't get as you have no  aggregation function it) comes for every order.id.
Maybe you should try a DISTINCT
                Thanks @nbk. This is working, but I cannot make sense of this SQL. Could you please educate me a bit about it?
– CodeForGood
                Sep 6, 2021 at 17:36
You select both orders.id and orders.customer_fk, but you group by orders.id only. When using group by in SQL, all other columns not present in the group by clause must be aggregate functions, since for example in this current case you group the results by the order id, meaning there can be only one row per unique order id among the results.
And something has to happen with the list of values of the other column that all belong to this one grouped order id, this is where the aggregations come in. If it is a number you can calculate the MIN/MAX/AVG etc. of these, but the simplest aggregate is to just count the matching results.
So if you wanted your query to return the number of order.customer_fk for each unique order.id, just add SELECT orders.id, COUNT(orders.customer_fk).
Otherwise, if you didn't intend to group your results, you can remove the GROUP BY clause, or replace it with an ORDER BY.
If you want to filter using aggregation functions use having.  However, I'm guessing that you just want to filter by the date:
SELECT o.id, o.customer_fk
FROM orders o INNER JOIN
     order_details od
     ON od.order_fk= o.id
WHERE o.payment_method IN ('AS','AC') AND 
      ((o.order_status = 'SHP' AND od.item_status='SHP' AND od.shipped_date <= '2021-08-07') OR
       (o.order_status = 'CAN' AND o.order_date <= '2021-08-07 09:56:18') 
      ) AND
      o.pii_status <> '1'
GROUP BY o.id
                On running your SQL, I got this error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND o.pii_status <> '1' GROUP BY o.id LIMIT 0, 25' at line 1
– CodeForGood
                Sep 6, 2021 at 17:18