Novice stuck with calculation - SQL Server Forums

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

Novice stuck with calculation

Author

Topic

jamessi88
Starting Member

4 Posts

Posted - 2012-01-19 : 14:23:50

Right,

I've been playing around with the standard invoice/customer database, got everything up and running just having some problems trying to calculate an invoice.

I have the following tables

Cant for the life of me write a statement to calculate an invoince for a customer. I want to select from Invoince the Customer Number, Products and quantity and times by the standard price in the Products table.

Can anyone point me in the right direction or possible help with some code. Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-19 : 15:07:45

The screen shot that you have posted is not readable even after zooming in. So this is just a guess:

SELECT
	i.CustomerNumber,
	i.ProductId,
	SUM(i.Quantity*p.StandardPrice) AS Amount
FROM
	Invoice i
	INNER JOIN Products p ON p.ProductId = i.ProductId
GROUP BY
	i.CustomerNumber,
	p.ProductId

jamessi88
Starting Member

4 Posts

Posted - 2012-01-19 : 15:45:00

Hey,
Thanks a lot for getting back to me,

SELECT
i.Customer_Number,
i.Product_No,
SUM(i.Quantity*p.Standard_Price) AS Amount
FROM
Invoice i
INNER JOIN Products p ON p.Product_No = i.Product_No
GROUP BY
i.Customer_Number,
p.Product_No

Edited to fit my database, doesn't seem to work get the param value pop up

Here's a better copy of my tables

jamessi88
Starting Member

4 Posts

Posted - 2012-01-19 : 17:36:42

Have you shorted the Invoice and Product to I and P ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-19 : 19:11:28

Yes, I aliased Invoice to i etc. I didn't realize that you were using Access. Not sure if the syntax is correct for Access, but the logic you need to use is as follows:

SELECT
	i.Customer_Number,
	o.Product_Number,
	SUM(o.Quantity*p.Standard_Price) AS Amount
FROM
	Invoice i
	INNER JOIN [ORDER] o ON o.Invoice_Number = i.Invoice_Number
	INNER JOIN Products p ON p.Product_Number = o.Product_Number
GROUP BY
	i.Customer_Number,
	o.Product_Number

jamessi88
Starting Member

4 Posts

Posted - 2012-01-19 : 19:24:26

Thanks, yeah might need to convert, http://stackoverflow.com/questions/794656/convert-tsql-to-ms-access-sql
but the logics looking good.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources