SQL SUM with GROUP clause giving errors

Question

I've been visiting this site for a while now and many of the responses on here has been most helpful. However, I'm now stuck with an SQL that I can't seem to find just the right solution for.

(the $packplant and $ym is already defined earlier in the program)

SELECT 
    A.in_house_supplier_cd,
    B.maker_cd,
    A.packing_plant_cd,
    A.parts_no, 
    substr(A.actual_delivery_date,1,6), 
    A.actual_delivered_qty 
FROM 
    TRN_DELIVERY_NO A, 
    TRN_PARTS B 
WHERE 
    A.ISSUE_NO = B.ISSUE_NO 
    AND A.PACKING_PLANT_CD = '$packplant' 
    AND B.PACKING_PLANT_CD = '$packplant' 
    AND A.PARTS_NO = B.PARTS_NO 
    AND A.IN_HOUSE_SUPPLIER_CD = B.IN_HOUSE_SUPPLIER_CD 
    AND A.ACTUAL_DELIVERY_DATE LIKE '$ym%' 
ORDER BY 
    in_house_supplier_cd, maker_cd, parts_no;

This sql works fine. However, what I need is that the "A.actual_delivered_qt" to be sum(A.actual_delivered_qty)... in other words, I need the sum of that particular parts and not individual quantities that were received.

When I add the "sum.." part (or even with adding a GROUP BY parts_no), the sql gives a "column ambiguously defined" error.

I believe that I've already assigned the correct table to each column and therefore would really appreciate it if someone could point out the errors as I've been stuck with this for quite a while now. Cheers!

What exactly did you try to put in the SUM and GROUP BY and which columns did it complain about ambiguity on? — brianestey, Dec 16 '11 at 03:52

score 1 · Accepted Answer · answered Dec 16 '11 at 04:07

1

You will need to add a GROUP BY statement, for example on parts_no, but then you will have an issue with the rest of the columns in your select statement.

For example, if you have 3 records for the same part no on different days and you are grouping by part_no and calculating the total number of items within that group number, the date no longer makes sense. The best you can do is select the max value from the date, but again, this doesn't make much sense.

You should think about what data really makes sense to include in the select statement when you are grouping by part_no and then revise the columns in the select statement to meet this new design.

answered Dec 16 '11 at 04:07

competent_tech

44,465
11
90
113

The "date" part is actually year-month (yyyymm). Therefore, what I'd like is to get the total of that month, regardless of what date/time the item was received. – Nax.S Dec 16 '11 at 04:19
Ok, I am not familiar with the precise syntax for sqlplus, but you should be able to add something like MAX to each of the non-summed columns and then sum the column(s) that you want totals for. – competent_tech Dec 16 '11 at 04:34
Was out of town for the weekend, so didn't get a chacne to update the post. I've managed to get the SQl query to work (got the response from another thread). Apparently, I had to add the whole "select" section (except for the "sum(...)" part) to the GROUP BY clause and it worked! Anyways..thank you all for your help and time : ) – Nax.S Dec 19 '11 at 01:37

score 0 · Answer 2 · answered Dec 16 '11 at 03:49

0

When you add the group by make sure you include the table alias ( like a.parts_no ). Can you add your "new" query including the sum and group by?

answered Dec 16 '11 at 03:49

Tuck

203
3
8

score 0 · Answer 3 · edited May 23 '17 at 12:07

Just FYI, according to PostgreSQL 9.1 Documentation - 3.5. Window Functions and Microsoft - OVER Clause (Transact-SQL) - (SQL 2008) (but they have links for MS SQL 2005 as well) GROUP BY isn't strictly needed. Just take a look at the Sample SQL queries

PostGreSQL:

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;

MS SQL:

USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);
GO

I've never used SQL Plus, but it looks like the OVER clause is supported there as well.

I wasn't aware of what SQL variant was being used until after I wrote most of that comment, sorry. But it does demonstrate `OVER` is commonly implemented. — JayC, Dec 16 '11 at 04:38

SQL SUM with GROUP clause giving errors

3 Answers3