simple query question

Question

hi all, I have a simple question: having a table where I have multiple orders for any number of vendors and a table with orders and amounts, how do I write the query that sums up the amounts by vendor. I tried: select vnd.VENDOR, sum(ord.Amount) as sumAmount from TBL_Vendor vnd inner join TBL_Orders ord on vnd.ORDER_ID = ord.ID group by VENDOR order by VENDOR but I am told " ... ord.Amount is invalid in the select list because it is not contained in either an aggregate function or the group by clause." Again, the tables are as follows: ID           Amount          Requester ===       ======         ======= 123         $200.00        Jack 456        $300.00         John 789        $400.00         Jesse OrderID        Vendor        VendorLocation ======        =====         =========== 456                 VND1            LOC1 789                 VND1            LOC1 123                 VND2            LOC2 The result of the query should be: Vendor         sumAmount =====        ========= VND1          700.00 VND2          200.00 Thanks, elsvieta

Answer 1

If you provide your table scripts, you may already find the issue. Please provide your table DDL scripts. Thanks.

Answer 2

The error message does not match the query you posted. But then again that query looks a little funny.

select vnd.VENDOR, sum(ord.Amount) as sumAmount 
from TBL_Vendor vnd 
inner join TBL_Orders ord on vnd.ORDER_ID = ord.ID 
group by VENDOR 
order by VENDOR

This query suggests that there is an OrderID in TBL_Vendor. Now, please bear in mind that I know nothing about your business and even less about your tables. But it does sound odd that there would be an OrderID in a Vendors table. Rather, I would expect Vendors to be a relatively small lookup table, and possibly the Orders table could have a VendorID. Then maybe, you may want a query like this:

select vnd.Name, sum(ord.Amount) as sumAmount 
from TBL_Vendor vnd 
inner join TBL_Orders ord on vnd.VendorID = ord.VendorID
group by vnd.Name
order by vnd.Name

Again, I don't know your tables, so I'm making things up.

Answer 3

Hi @elsvieta

ord.Amount is invalid in the select list because it is not contained in either an aggregate function or the group by clause

This error message indicated that you need to put ord.Amount inside the aggregate function SUM(). However the query you post is wrote correctly.

I tested on my side and found no issue. Please check the sample below:

CREATE TABLE TBL_Vendor(OrderID INT,Vendor VARCHAR(20),VendorLocation VARCHAR(20))
INSERT INTO TBL_Vendor VALUES
(456,'VND1','LOC1'),(789,'VND1','LOC1'),(123,'VND2','LOC2')
CREATE TABLE TBL_Orders(ID INT,Amount MONEY,Requester VARCHAR(20))
INSERT INTO TBL_Orders VALUES
(123,200,'Jack'),(456,300,'John'),(789,400,'Jesse')

select vnd.VENDOR, sum(ord.Amount) as sumAmount 
from TBL_Vendor vnd inner join TBL_Orders ord on vnd.OrderID = ord.ID 
group by VENDOR 
order by VENDOR

DROP TABLE TBL_Vendor,TBL_Orders

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.