Don LaCombe Don LaCombe - 1 month ago 12
SQL Question

SQL MS ACCESS Create an Alias using Multiplication of Columns

Hello I am having trouble making a new Column "Alias" in my SQL code by multiplying two column values.

Question:



  1. Compose a query to show every product that has a value of $400 or more. The value of each product is defined as the quantity on hand
    multiplied by the unit price of that product. In the result table,
    show product ID, unit price, quantity on hand, and the product value.
    Sort the results by the product value in descending order.




My Code:

Select Product_ID, Unit_Price, On_Hand, **Unit_Price * On_Hand as Product_Value**
From Product_T
Where Product_Value >= 400.00
Group by Product_ID
Order by (Product_Value) desc


I keep getting an error message saying:

"Your Query does not include the specified expression 'unit price' as part of an aggregate function"

Any idea on how to overpass this?

Answer

I don't think an aggregation is needed. If not, you need to repeat the expression in the where (or use a subquery):

Select Product_ID, Unit_Price, On_Hand,
       (Unit_Price * On_Hand) as Product_Value
From Product_T
Where (Unit_Price * On_Hand) >= 400.00
Order by (Unit_Price * On_Hand) desc;

If an aggregation is needed, then you want a having clause:

Select Product_ID, 
       sum(Unit_Price * On_Hand) as Product_Value
From Product_T
Group by Product_ID
Having sum(Unit_Price * On_Hand) >= 400.00
Order by sum(Unit_Price * On_Hand) desc;
Comments