SQL JOIN, GROUP BY, DATES-CodePudding

I have 3 tables and I have trouble with the quantity. I will apply my solution down. SUM and GROUP BY are not working.

PRODUCT_INFORMATION

PRODUCT_ID  
PRODUCT_NAME  
PRODUCT_DESCRIPTION  
PCATEGORY_ID  
PRODUCT_STATUS  
WARRANTY_PERIOD

ORDERS

ORDER_ID  
ORDER_DATE  
ORDER_MODE  
CUSTOMER_ID  
ORDER_STATUS  
ORDER_TOTAL

ORDER_ITEMS

ORDER_ID   
LINE_ITEM_ID  
PRODUCT_ID
UNIT_PRICE  
QUANTITY

• whose name (PRODUCT_NAME) does not contain the characters ‘_’ and ‘<’.

• the total number of products sold (QUANTITY column of table oe.ORDER_ITEMS) of a given type to be greater than 200.

The list should contain the following columns:

• PRODUCT_NAME (by oe.PRODUCT_INFORMATION)

• TOTAL_QUANTITY - the total number of products sold of a given type

SELECT p.PRODUCT_NAME, sum(oi.QUANTITY), p.WARRANTY_PERIOD AS WARRANTY, o.ORDER_MODE
FROM PRODUCT_INFORMATION p 
    LEFT JOIN ORDER_ITEMS oi ON p.PRODUCT_ID=oi.PRODUCT_ID
    LEFT JOIN ORDERS o ON o.ORDER_ID = oi.ORDER_ID
WHERE (p.PRODUCT_NAME NOT LIKE '%<%' 
AND p.PRODUCT_NAME NOT LIKE '%\_%' ESCAPE '\')
AND oi.QUANTITY>200
group by p.PRODUCT_NAME;

CodePudding user response：

What does "... are not working" mean? Did you get any error? If so, which one?

What is obvious, is that GROUP BY clause has to contain all non-aggregated columns. As problem doesn't require them to be displayed, I removed them from the select column list (both p.WARRANTY_PERIOD and o.ORDER_MODE).

Furthermore, you should move the "quantity > 200" condition from WHERE into the HAVING clause.

Finally, in order to avoid characters to be escaped, I switched from NOT LIKE to INSTR.

Something like this:

  SELECT p.product_name, 
         SUM (oi.quantity) sum_quantity
    FROM product_information p
         LEFT JOIN order_items oi ON p.product_id = oi.product_id
         LEFT JOIN orders o ON o.order_id = oi.order_id
   WHERE INSTR (p.product_name, '<') = 0
     AND INSTR (p.product_name, '_') = 0
GROUP BY p.product_name
  HAVING SUM (oi.quantity) > 200;

CodePudding user response：

Your query selects columns which are not in the GROUP BY clause, which is usually not a problem with MySQL, but would be with any other vendor.

Since you don't require the two last columns in your output, I suggest you just get rid of them.

Also, MySQL supports regexes, which are more suited to your problem than LIKE in my opinion.

SELECT p.PRODUCT_NAME, SUM(oi.QUANTITY)
FROM PRODUCT_INFORMATION p 
LEFT JOIN ORDER_ITEMS oi ON p.PRODUCT_ID=oi.PRODUCT_ID
LEFT JOIN ORDERS o ON o.ORDER_ID = oi.ORDER_ID
WHERE p.PRODUCT_NAME REGEXP '[_<]' = 0
GROUP BY p.PRODUCT_NAME
HAVING SUM(oi.QUANTITY) > 200;

Edit : As pointed out by @Littlefoot, the quantity condition should be tested after the grouping and not before, and should therefore be in the HAVING clause rather than in the WHERE clause.