Using avg function for new constructed column?
-
Hi friends, I have a two columns as unitprice and quantity in salesreport table. I am constructing new column by multplying this two column. I gave the name of sales for this new column. I want to ge average of sales column. I tried below code but program says the column name of sales is invalid. select unitprice,quantity,unitprice*quantitiy as sales from salesreport where sales>avg(sales)
-
Hi friends, I have a two columns as unitprice and quantity in salesreport table. I am constructing new column by multplying this two column. I gave the name of sales for this new column. I want to ge average of sales column. I tried below code but program says the column name of sales is invalid. select unitprice,quantity,unitprice*quantitiy as sales from salesreport where sales>avg(sales)
I think you have to change the structure of your query a bit in order to get the results you would like. First off, Sales is a calculated column, so if you would like to filter by that value, you will have to filter using the calculation instead of the alias you give to that calculation. Secondly, to get the average sales, you'll have to include a sub-query in your where clause that will calculate the average. This subquery will then be used to compare values against to determine if it meets the criteria in your where clause Your query should look similar to:
select unitprice, quantity, unitprice * quantitiy as sales from salesreport where **(unitprice * quantitiy)** > **(select avg(unitprice * quantitiy) from salesreport)** -
I think you have to change the structure of your query a bit in order to get the results you would like. First off, Sales is a calculated column, so if you would like to filter by that value, you will have to filter using the calculation instead of the alias you give to that calculation. Secondly, to get the average sales, you'll have to include a sub-query in your where clause that will calculate the average. This subquery will then be used to compare values against to determine if it meets the criteria in your where clause Your query should look similar to:
select unitprice, quantity, unitprice * quantitiy as sales from salesreport where **(unitprice * quantitiy)** > **(select avg(unitprice * quantitiy) from salesreport)**Thanks so much for your quick and detailed response but i have another problem. First of all i wonder why we can't write avg(sales) instead of avg(unitprice * quantitiy). I am working with nortwind sample. I have the column sum("order details".unitprice*quantity*(1-discount)) as totalsales (I don't write all all code, if it is not clear what I want , i can write all my code trying.) as accumulated new column. I want to get average of this.and I used group by function also. I wrote avg(sum("order details".unitprice*quantity*(1-discount)) ) .Program says you can not sum in....)
-
I think you have to change the structure of your query a bit in order to get the results you would like. First off, Sales is a calculated column, so if you would like to filter by that value, you will have to filter using the calculation instead of the alias you give to that calculation. Secondly, to get the average sales, you'll have to include a sub-query in your where clause that will calculate the average. This subquery will then be used to compare values against to determine if it meets the criteria in your where clause Your query should look similar to:
select unitprice, quantity, unitprice * quantitiy as sales from salesreport where **(unitprice * quantitiy)** > **(select avg(unitprice * quantitiy) from salesreport)**I used below code series and I got "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference." error. select employees.Firstname,employees.lastname,orders.OrderID, sum("order details".unitprice*quantity*(1-discount)) as Sales from (Employees inner join orders on employees.employeeID=orders.employeeID) inner join "order details" on orders.orderID="order details".orderID where sum("order details".unitprice*quantity*(1-discount))>(select avg(sum("order details".unitprice*quantity*(1-discount))) from "order details") group by employees.lastname,orders.orderID,employees.firstname