Need assistance with SQL Query

I need a query to return the totals for the items of a project. The ProjectItems table has the following columns.

ID
ProjectID
Qty
Cost
List
Discount

So I need it to return Total Cost, List Price and Sell Price.

Total Cost would be the sum of (Qty * Cost) in each row
List Price would be the sum of (Qty * List) in each row
Sell Price would be the sum of ((1 - Discount) * (Qty * List)) in each row

Screen Shot Of Table
BasicfarmerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve WalesSenior Database AdministratorCommented:
This should do the job for you

select ID, ProjectID, Qty, Cost, List, Discount, Qty * Cost as TotalCost, Qty * List as ListPrice, ((1 - Discount) * (Qty * List)) as SellPrice
from ProjectItems

Open in new window

BasicfarmerAuthor Commented:
Yes, that will give me the Totals for each row, but what I need is the Sum of the rows based on those calculations.
Scott PletcherSenior DBACommented:
SELECT Total_Cost = SUM(Qty * Cost),
    List_Price = SUM(Qty * List),
    Sell_Price = SUM((1 - Discount) * (Qty * List))
FROM ProjectItems
PortletPaulEE Topic AdvisorCommented:
if you include a GROUP BY clause you can get the result for each project

SELECT
      ProjectID
    , SUM(Qty * Cost) AS Total_Cost
    , SUM(Qty * List) AS List_Price
    , SUM((1 - Discount) * (Qty * List)) AS Sell_Price
FROM ProjectItems
GROUP BY ProjectID

and/or add a where clause depending on your need.
(It isn't clear from the question which approach you are looking for)


SELECT
      ProjectID
    , SUM(Qty * Cost) AS Total_Cost
    , SUM(Qty * List) AS List_Price
    , SUM((1 - Discount) * (Qty * List)) AS Sell_Price
FROM ProjectItems
GROUP BY ProjectID
WHERE ProjectID = 3

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BasicfarmerAuthor Commented:
Thanks Guys, I was stuck on the Group By. That is exactly what I needed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.