I have tried and I cant figure out why I keep getting an error. I have looked over the commas, parenthesis and semicolons and don't see any issues.

SELECT INVOICE.INVOICE_NUMBER, INVOICE.BUSINESS_NAME, INVOICE_ITEM.PRODUCT_SKU AS PRODUCT_SKU, INVOICE_ITEM.QUANTITY_ORDERED AS QUANTITY
FROM INVOICE_ITEM
INNER JOIN INVOICE ON INVOICE_ITEM.INVOICE_NUMBER = INVOICE.INVOICE_NUMBER
GROUP BY BUSINESS
ORDER BY QUANTITY_ORDERED DESC
WHERE QUANTITY_ORDERED > (
SELECT DISTINCT BUSINESS_NAME AS BUSINESS, PRODUCT_SKU, SUM(QUANTITY_ORDERED)
FROM INVOICE_ITEM group by BUSINESS_NAME, PRODUCT_SKU
WHERE PRODUCT_SKU = 'UJT123' AND QUANTITY >= 3
);

I'm trying to get an output of invoice number - business name - and quantity ordered more than 3 (can be across multiple orders).

CodePudding user response：

Your first issue is because the WHERE clause goes before the GROUP BY and the ORDER BY clauses.

Your second issue is because you are not either including the columns in the GROUP BY clause or aggregating the columns.

What you appear to want to do is to use a HAVING clause:

SELECT LISTAGG(i.INVOICE_NUMBER, ',') WITHIN GROUP (ORDER BY invoice_number)
         AS invoices,
       i.BUSINESS_NAME,
       ii.PRODUCT_SKU,
       SUM(ii.QUANTITY_ORDERED) AS QUANTITY
FROM   INVOICE_ITEM ii
       INNER JOIN INVOICE i
       ON ii.INVOICE_NUMBER = i.INVOICE_NUMBER
WHERE  PRODUCT_SKU = 'UJT123'
GROUP BY
       i.business_name,
       ii.product_sku
HAVING SUM(ii.quantity_ordered) > 3
ORDER BY quantity DESC;

However, without having an example of your sample data and the expected output it is difficult to be sure.

CodePudding user response：

• where clause is on wrong place
• you're trying to compare quantity to 3 columns subquery returns, and that won't work - perhaps you'll have to correlate subquery to table(s) in the from clause
• group by business is most probably wrong; you'll have to put all columns you select in there, or not use group by (substitute it with distinct)

Something like this:

  SELECT invoice.invoice_number,
         invoice.business_name,
         invoice_item.product_sku AS product_sku,
         invoice_item.quantity_ordered AS quantity
    FROM invoice_item ii
         INNER JOIN invoice
            ON invoice_item.invoice_number = invoice.invoice_number
   WHERE quantity_ordered > (  SELECT SUM (quantity_ordered)
                                 FROM invoice_item ii1
                                WHERE     ii1.business_name = ii.business_name
                                      AND ii1.product_skup = ii.product_sku
                                      AND product_sku = 'UJT123'
                                      AND quantity >= 3
                             GROUP BY business_name, product_sku)
ORDER BY quantity_ordered DESC;