simple query question

elsvieta 20 Reputation points
2023-04-04T16:42:35.11+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,912 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Jingyang Li 5,891 Reputation points
    2023-04-04T20:53:57.1366667+00:00

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

    0 comments No comments

  2. Erland Sommarskog 102.3K Reputation points
    2023-04-04T21:43:21.9033333+00:00

    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.

    0 comments No comments

  3. CosmogHong-MSFT 23,946 Reputation points Microsoft Vendor
    2023-04-05T03:18:03.5166667+00:00

    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.

    0 comments No comments