1

I hope this doesn't violate any rules (i'm new, and yes i did check the FAQ).

But i have the following code 

SELECT DISTINCT PO_NUMBER, PO_TRACKING_NO, SUPPLIER, MIN(PO_QUANTITY),    
      SUM(SHIPPED_WT), PO_SHIPMENT_TO 
FROM VW_TRAFFIC_PO_SIDE
WHERE SYSDATE > PO_SHIPMENT_TO AND
      (PO_QUANTITY > SHIPPED_WT OR PO_TRACKING_NO IS NULL) 
GROUP BY PO_NUMBER, PO_TRACKING_NO, SUPPLIER, PO_SHIPMENT_TO
HAVING PO_QUANTITY > SUM(SHIPPED_WT)

It is suppose to do the following: Display 1 PO# per Tracking No, [this is the distinct, and it is correct, i was able to compiple the code successfully]...next, and this is the tricky part (this is the SUM, MIN, GROUP BY and HAVING parts): I needed to combine the SHIPPED_WT of any PO_NUMBER (that is displayed), and to show that combined number if it is less than the PO_Quantity

the where statement is also correct, as i compiled it successfully, it was only when i added the above part that i ran into trouble.

EDIT: the above code NOW compile (thanks to the answer below)

HOWEVER, my logic is wrong, and it does not do what i would want it to do..the one thing it does do correctly is grab 1 PO_NUMBER with 1 PO_TRACKING_NO (there can be duplicate po_numbers as long as the PO_tracking_no is different, and there can be duplicate tracking as long as the number is different)

EDIT: Below is what the relevant columns look like, and i will explain what is wrong with it (and what i tried to do with the code that was revised

PO_NUMBER    PO_TRACKING_NO      MIN(PO_QUANTITY)  SUM(SHIPPED_WT)
123          C100                1000              750
123          C101                1000              250

the code was suppose to take records like this, and combine the SHIPPED_WT into one number (1000), and because that new number is = to the PO_QUANTITY, it should not appear in the query....in addition, the WHERE of PO_TRACKING_NO ISNULL is not working (all records with a null value in that field should be displayed, assuming it isn't a duplicate of a PO_NUMBER and PO_TRACKING_NO)

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
socool111
  • 113
  • 3
  • 11
  • Essentially i used the first answer to compile the code, but see my comments under it...my logic was incorrect and did not do what i wanted it to do :( – socool111 Jan 08 '13 at 13:00
  • if the answer below is good, accept it. If not, can you say a little about the table columns that you are selecting from. A bit of example data and column names. – boisvert Jan 08 '13 at 13:25
  • OK, so the below answer is the correctly revised version of my code...but my code was wrong, in that it didn't do what i wanted..here is what some of the stuff looks like (i took out the columns that are not relevant to the problem)..i will edit it to my question – socool111 Jan 08 '13 at 13:41

2 Answers2

3

You can't have aggregated values in the group by clause like min, max, sum...etc...

SELECT PO_NUMBER, PO_TRACKING_NO, SUPPLIER, 
MIN(PO_QUANTITY), SUM(SHIPPED_WT), PO_SHIPMENT_TO 
FROM VW_TRAFFIC_PO_SIDE WHERE SYSDATE > PO_SHIPMENT_TO 
AND (PO_QUANTITY > SHIPPED_WT OR PO_TRACKING_NO IS NULL) 
GROUP BY PO_NUMBER, PO_TRACKING_NO, SUPPLIER, PO_SHIPMENT_TO
HAVING MIN(PO_QUANTITY) > SUM(SHIPPED_WT)

Reference to read on the error: ORA-00934: Group function not allowed here || Selecting MIN(Salary) of highest paid dept.

EDIT: ADDING sqlfiddle demo as OP still comments with doubts.

The above sample demo is in SQL Server as it's the nearest I could get closer to SQL since I dont see you are specifying the RDBMS... Because in MYSQL we have a bit loose behaviou towards group_by...comared to SQL Server, TSQL, Oracle and MS ACCESS SQL. So the above query is more or less an ANSI standard but you may need to change few things like how dates are inserted into the table, GetDate()function.

But frankly, some of the conditions in your query doesn't really make sense though.

E.g. AND PO_QANTITY > SHIPPED_WT then you do HAVING MIN(PO_QUANTITY) > SUM(SHIPPIED_WT)... Anyway as it deems for your requirements ;)

Community
  • 1
  • 1
bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • i tried that, i got a "not a group by expression" and i was told that that meant that i didn't have all the select fields in there, so that's when i added the MIN, Sum – socool111 Jan 08 '13 at 12:31
  • So you need to include all the fields in the select query that do not use and aggregate function :) – bonCodigo Jan 08 '13 at 12:33
  • @bonCodigo use `IS NULL` predicate, instead of the `= null` – Mahmoud Gamal Jan 08 '13 at 12:33
  • right, but that's why i was confsued and asked the question, because i thought i included all of them, PO_NUMBER, PO_TRACKING_NO, SUPPLIER, PO_SHIPMENT_TO...what am i missing? – socool111 Jan 08 '13 at 12:34
  • grr..apparently i didn't change it the right way, it's working now :)..i'm just going to go through it and make sure that i did what i wanted to do :D – socool111 Jan 08 '13 at 12:38
  • Unfortunately, it did not...i still have records whose got the same PO_NUMBER, and whose SHIPPED_WT, when added together = PO_QUANTITY...in addition, it doesn't return the recods where PO_SHIPMENT_TO are null...so i think my logic is wrong, which brings us back to the beginning :( – socool111 Jan 08 '13 at 12:42
  • @socool111 Sorry I had to be away. I am creating a sqlfiddle. So it's easier for your to see it. Will update the answer. – bonCodigo Jan 08 '13 at 13:46
  • @bonCodigo . . . Could you remove the `distinct` from the `select`? It is redundant when you have a `group by`. – Gordon Linoff Jan 08 '13 at 14:15
  • @GordonLinoff by all means sir :) to make it crystal clear correct syntax. – bonCodigo Jan 08 '13 at 14:21
  • @socool111 the above answer and the reference ..all based on your initial question. Since you have updated the question, I come to think....hmm... – bonCodigo Jan 08 '13 at 14:29
  • 1
    thanks for everyone's help, i really appreciate it! Helped me understand SQL further – socool111 Jan 08 '13 at 14:53
1

I don't think you want to group by PO_TRACKING_NO. Also, your WHERE clause could accidentally exclude rows that you want; it's the aggregates that you want to check (the HAVING clause). If you replace the NULLs with 0s using COALESCE then the HAVING clause should cover all of your cases:

SELECT PO_NUMBER, MIN(PO_QUANTITY) AS PO_QUANTITY, SUM(COALESCE(SHIPPED_WT, 0)) AS SHIPPED_WT
FROM VW_TRAFFIC_PO_SIDE
WHERE SYSDATE > PO_SHIPMENT_TO
GROUP BY PO_NUMBER
HAVING MIN(PO_QUANTITY) > SUM(COALESCE(SHIPPED_WT, 0))
jjoelson
  • 5,771
  • 5
  • 31
  • 51