simple query question

Question

Post reply

simple query question

axes2000

SSC-Addicted

Points: 433
More actions
April 4, 2023 at 4:30 pm
Go to Answer
#4169034

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

Vedat ÖZER

SSC Enthusiast

Points: 103

April 4, 2023 at 5:17 pm

Answer

#4169060

If(OBJECT_ID('tempdb..#TBL_Orders ') Is Not Null)
Begin
    Drop Table #TBL_Orders 
End

CREATE TABLE #TBL_Orders 
(
  ID  INT,
  AMOUNT DECIMAL(19,2),
  Requester   NVARCHAR(100)

)
INSERT INTO #TBL_Orders
VALUES('123','200.00','Jack'),
      ('456','300.00','John'),
  ('789','400.00','Jesse')

If(OBJECT_ID('tempdb..#TBL_Vendor') Is Not Null)
Begin
    Drop Table #TBL_Vendor 
End

CREATE TABLE #TBL_Vendor 
(
  OrderID  INT,
  Vendor NVARCHAR(100),
  VendorLocation   NVARCHAR(100)
)

INSERT INTO #TBL_Vendor
VALUES('456','VND1','LOC1'),
('789','VND1','LOC1'),
('123','VND2','LOC2')

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

Allah bize yeter, O ne güzel vekildir.

vedatoozer@gmail.com

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 1

In the following, you have an underscore in the code that does not exist in the TBL_Vendor table.

on vnd.ORDER_ID = ord.ID

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ratbak Ten Centuries Points: 1350 More actions · Answer 2

That would happen if you did not have ord.Amount wrapped in the aggregate SUM function.

It should work w/ the query listed.

drew.allen SSC Guru Points: 76960 More actions · Answer 3

Instead of the wordy

If(OBJECT_ID('tempdb..#TBL_Orders ') Is Not Null)
Begin
    Drop Table #TBL_Orders 
End

I recommend switching to the shorter and more intuitive

DROP TABLE IF EXISTS #TBL_Orders

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 4

ratbak wrote:

That would happen if you did not have ord.Amount wrapped in the aggregate SUM function.
It should work w/ the query listed.

It can't... there's no ORDER_ID column in the Vendor table. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18313 More actions · Answer 5

Jeff Moden wrote:

ratbak wrote:
That would happen if you did not have ord.Amount wrapped in the aggregate SUM function.
It should work w/ the query listed.
It can't... there's no ORDER_ID column in the Vendor table. 😀

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

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 6

Jonathan AC Roberts wrote:

Jeff Moden wrote:
ratbak wrote:
That would happen if you did not have ord.Amount wrapped in the aggregate SUM function.
It should work w/ the query listed.
It can't... there's no ORDER_ID column in the Vendor table. 😀
The error the OP posted is "... ord.Amount is invalid in the select list because it is not contained in either an aggregate function or the group by clause."

Yep... I know that. The posted code will not produce that error.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)