how to select with join and sum

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to select with join and sum

Author

Topic

elic05
Yak Posting Veteran

62 Posts

Posted - 2012-06-14 : 05:25:05

I have two tables
Prices - columns: sellerCode,price,date
Purchases - columns: sellerCode,date

the Prices table will get a new line every time a seller changes the price.

I want to know how much money I got.

I have to find what was the price of the specific seller on the date of the purchase for the specific purchase, and to sum the money i have in all the purchases.

how can it be done?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-14 : 05:27:59

please give table structure, sample data and wanted output

No, you're never too old to Yak'n'Roll if you're too young to die.

elic05
Yak Posting Veteran

62 Posts

Posted - 2012-06-14 : 05:45:54

[code]

CREATE TABLE [dbo].[Purchases](
[UserName] [nvarchar](256) NOT NULL,
[Code] [int] NOT NULL,
[PicNum] [smallint] NOT NULL,
[Date] [smalldatetime] NOT NULL,
[SellerCode] [smallint] NULL
)
[/code]
[code]
CREATE TABLE [dbo].[Prices](
[SellerCode] [smallint] NOT NULL,
[Price] [float] NULL,
[Vat] [float] NULL,
[Earn] [float] NULL,
[Date] [smalldatetime] NOT NULL
)
[/code]

data for prices
1,150.2,20,130.2,10/3/2009
1,120.52,15,105.52,3/7/2010
2,200,60,140,10/2/2009

data for purchases
john,112112,10,12/12/2009,1
elie,5454454,84,8/8/2010,1
joe,5645465,45,2/5/2010,2

I want to get the total sum of money I have in my account

the price i got for every purchase depends on hwo was the seller (sellerCode) and the date of the purchase
I have to get the price paid for every row in the purchases table
and sum all the result
thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 06:04:35

select pu.UserName, sum(pr.Price), sum(pr.Vat), sum(pr.Earn)
from Purchases pu
join Prices pr
on pu.SellerCode = pr.SellerCode
and pu.Date = pr.Date
group by pu.UserName

Bit surprised there's no quantity in the Purchases - in fact would expect an order table with rows split into order details.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

elic05
Yak Posting Veteran

62 Posts

Posted - 2012-06-14 : 06:08:24

the problem is the pu.date is not equal to pr.date
because the prices are updated from time to time
and the purchases are done sometimes after the price was updated
(but before the next update)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources