Group By Problems

[ensorg]

Hi,

I hope someone here can help me out.I'm trying to learn SQL and am having a few problems with the Group By statement. I can get it to work with only one table and no joins but when I try and bring other tables into the query with where statements I hit problems. I have included my code below.

Thanks All!

SELECT PO_HEADER.ORDERNO, PO_HEADER.SUPPREF, PO_DETAIL.NON_STK_RF, PO_DETAIL.PART, Sum(PO_DETAIL.QTY_ORD), Sum(PO_DETAIL.QTY_REC), QTY_ORD-QTY_REC, PO_HEADER.SUPPLIER, SUPPLIER.SUP_NAME, PART.DESCR, PO_DETAIL.PUR_PRICE, PO_DETAIL.DUE
FROM PUBLIC.PART PART, PUBLIC.PO_DETAIL PO_DETAIL, PUBLIC.PO_HEADER PO_HEADER, PUBLIC.SUPPLIER SUPPLIER
WHERE PO_HEADER.ORDERNO = PO_DETAIL.ORDERNO AND PO_HEADER.SUPPLIER = SUPPLIER.SUP_NO AND PO_DETAIL.PART = PART.PART_NO AND ((PO_HEADER.SUPPREF Not Like 'E%') AND (PO_DETAIL.NON_STK_RF Is Null) AND (QTY_ORD-QTY_REC>0))
GROUP BY PO_HEADER.ORDERNO, PO_HEADER.SUPPREF, PO_DETAIL.NON_STK_RF, PO_DETAIL.PART, PO_HEADER.SUPPLIER, SUPPLIER.SUP_NAME, PART.DESCR, PO_DETAIL.PUR_PRICE, PO_DETAIL.DUE

 

[PruSQLer]

Just FYI, it helps if you include the error message you got. One error I spotted was that one of the fields in your Select statement, QTY_ORD-QTY_REC, is not in your Group by statement. I don't know if it's more than that or not.

By the way, the unwritten rule of SQL design says put each field or join on a separate line. That makes it much easier to read and debug.

 

[ensorg]

Sorry it didn't give me an error message, it just hung. I tried placing the QTY_ORD-QTY_REC into the group by but didn't make much difference, so i removed it totally from the group by and the select, ran the query and it worked!

 

[blom0344]

Hello ensorg,

Generally speaking , if your SQL makes an aggregate on measures , the group by should include the dimensions over which you aggregate. In your example you aggregate 2 measures, while keeping a third measure non-aggregated (QTY_ORD - QTY_REC). If you think this through , you will find that it does not make sense at all, cause adding the detail-measure will not give you the aggregates you are trying to establish. (Not to mention what it does with performance , anyway) The fact that removing it gives positive results is fixing what was wrong with the query in the first place !!! T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com