SQL Sum a calculated field

[Lol999]

I have a simple query I'm trying to finish off and it could be idiocy or just tiredness but I just can't get it to work!
I have two fields, Tbl_Issue.Qty_Issue and Tbl_PPE.Unit_Price.
I can get a query to return a subtotal for each instance of clothing being issued but I CANNOT get it to calculate the total cost!
Can someone please put me out of my misery

SELECT Tbl_PPE.PPE_PPE_Description, Tbl_PPE.PPE_PPE_Category,Tbl_Issue.Issue_PPE_ID, Tbl_Issue.Issue_PPE_Size, Tbl_Issue.Qty_Issue, ( [Tbl_Issue.Qty_Issue] * [Tbl_PPE.Unit_Price]) As SubTotal FROM Tbl_PPE INNER JOIN Tbl_Issue ON Tbl_PPE.PPE_ID = Tbl_Issue.Issue_PPE_ID;

many thanks, Lol

 

[plog]

Sounds like you need an aggregate query: http://cas.sdss.org/dr7/en/help/howto/search/aggfunctions.asp

You SUM the field you want to add up, then put all the other SELECT fields in a GROUP BY clause.

When that doesn't work, please post back here sample data to demonstrate what you hope to end up with. Provide 2 sets of data:

A. Starting sample data from your tables. Provide field and table names and enough sample data to cover all cases.

B. Expected results from A. Show me what you expect your ultimate query to return when you feed it the data in A.

 

[isladogs]

I have a simple query ...

SELECT Tbl_PPE.PPE_PPE_Description, Tbl_PPE.PPE_PPE_Category,Tbl_Issue.Issue_PPE_ID, Tbl_Issue.Issue_PPE_Size, Tbl_Issue.Qty_Issue, ( [Tbl_Issue.Qty_Issue] * [Tbl_PPE.Unit_Price]) As SubTotal FROM Tbl_PPE INNER JOIN Tbl_Issue ON Tbl_PPE.PPE_ID = Tbl_Issue.Issue_PPE_ID

Not a solution but a comment.
It would be a lot simpler, less typing and also easier to read if you changed your naming convention

E.g. tbl_PPE.PPE_PPE_Category

Better to get rid of pointless repetition and get rid of underscores
E.g tblPPE.Category

 

[Lol999]

Not a solution but a comment.
It would be a lot simpler, less typing and also easier to read if you changed your naming convention

E.g. tbl_PPE.PPE_PPE_Category

Better to get rid of pointless repetition and get rid of underscores
E.g tblPPE.Category

Yeah I've been playing with that although perhaps it's my twisted brain but I found it easy to follow

 

[Lol999]

Cheers PLog, I'll look at it after the weekend. I have my hours in this week so I'm focusing on other things, such as life for a few days

Thanks, Lol

Sounds like you need an aggregate query: http://cas.sdss.org/dr7/en/help/howto/search/aggfunctions.asp

You SUM the field you want to add up, then put all the other SELECT fields in a GROUP BY clause.

When that doesn't work, please post back here sample data to demonstrate what you hope to end up with. Provide 2 sets of data:

A. Starting sample data from your tables. Provide field and table names and enough sample data to cover all cases.

B. Expected results from A. Show me what you expect your ultimate query to return when you feed it the data in A.