[SQL] Aggregate Functions - Tilted Forum Project Discussion Community

aa1037 · 04-19-2006, 09:20 PM

I'm currently working on a lab for my database class and I've run into a snag. The problem is as follows:

Quote:

Management needs to adjust the insurance value of the total product inventory in case of fire or other peril. They need the dollar value for the total inventory value (Product Quantity times Product Unit Cost), average product cost and potential lost revenue (Product Quantity times Suggested Price.) Be sure to use aliases. Hint: you’ll only have one row with 3 values for this query.

Now I've written the following so far:

Code:

SELECT SUM (ProductQuant*ProductCost) 'Inventory Value', AVG (ProductCost) 'Average Product Cost', (ProductQuant*ProductPrice) 'Potential Lost Revenue'
	FROM Product;

and I'm getting the following error:

Quote:

Server: Msg 8118, Level 16, State 1, Line 1
Column 'Product.ProductQuant' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'Product.ProductPrice' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Now I know I need to use GROUP BY some how, but I'm not quite sure how.

aa1037 · 04-19-2006, 09:23 PM

I GOT IT! The following code yielded the proper results:

Code:

SELECT SUM (ProductQuant*ProductCost) 'Inventory Value', AVG (ProductCost) 'Average Product Cost', SUM (ProductQuant*ProductPrice) 'Potential Lost Revenue'
	FROM Product;

04-19-2006, 09:23 PM	#2 (permalink)
aa1037 Insane Location: New York	I GOT IT! The following code yielded the proper results: Code: SELECT SUM (ProductQuantProductCost) 'Inventory Value', AVG (ProductCost) 'Average Product Cost', SUM (ProductQuantProductPrice) 'Potential Lost Revenue' FROM Product;