Kjc21793 Kjc21793 - 10 days ago 8
SQL Question

Add Column to Query From Same Table, Update Values in Duplicated Column?

I'm trying to figure this one out. In this scenario, gas prices from a particular city in this database are increasing, so they will need to raise their prices 20%. I'm supposed to create a query that will display what the new price will be from that city only. Here is what it is supposed to look like:

enter image description here

Here is my code:

Select ProductID, tblProduct.ProductType, Price, SUM((Price *.20)+Price) AS 'Increased Price'
From tblProduct join tblCompany
On tblProduct.CompanyID = tblCompany.CompanyID
Where tblCompany.CompanyID IN
(Select CompanyID
From tblCompany
Where City = 'Kalamazoo')
Order By ProductID


However, when I go to execute the code, I get the following error:


Msg 8120, Level 16, State 1, Line 1
Column 'tblProduct.ProductID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


I can't figure out what I'm doing wrong. Can anyone enlighten me?

Answer

You dont really need the SUM function if you are only calculating the 20% price increase...Do this instead

Select ProductID, tblProduct.ProductType, Price, ((Price *.20)+Price) AS 'Increased Price'
From tblProduct join tblCompany
    On tblProduct.CompanyID = tblCompany.CompanyID
Where tblCompany.CompanyID IN
    (Select CompanyID
    From tblCompany
    Where City = 'Kalamazoo')
Order By ProductID