Posted: 1/25/2011 2:22:37 PM EDT
|
This works:
SELECT AVG(QuantityOnHand) AS AVGquantity FROM INVENTORY WHERE Warehouse IN (SELECT Warehouse FROM WAREHOUSE WHERE Manager='Smith'); But this doesnt: SELECT Warehouse, AVG(QuantityOnHand) AS AVGquantity FROM INVENTORY WHERE Warehouse IN (SELECT Warehouse FROM WAREHOUSE WHERE Manager='Smith'); Access gives me error "You tried to execute a query that does not include the specified expression 'Warehouse' as part of an aggregate function" I dont understand why its wrong as Warehouse is a column title in Inventory |
|
Quoted:
I would say it is because warehouse is a table. rather than a field in the table containing data. But it has been a long time since I've even thought about SQL. Well there is a table called WAREHOUSE, but the INVENTORY table has a column titled 'Warehouse' There is an almost identical example of successfully doing this in the book, instead of using AVG like Im doing it uses COUNT(*) |
|
The average function is an aggregate function, so it spans many rows. You get the average of all the warehouses that are managed by Smith. And you're going to get it in one row.
You cannot select all of the Warehouse names (or IDs) in one field. If you're looking to get averages by manager, link them relationally and select the warehouse manager's name, and the average, and include a GROUP BY clause. |
|
Quoted: The average function is an aggregate function, so it spans many rows. You get the average of all the warehouses that are managed by Smith. And you're going to get it in one row. You cannot select all of the Warehouse names (or IDs) in one field. If you're looking to get averages by manager, link them relationally and select the warehouse manager's name, and the average, and include a GROUP BY clause. Yep pretty much exactly what he said, pretty much and time you use a function like sum or avg you are going to have to include a group by statement if you are selecting any columns other than the field you are performing the calculation on. |
This should work
I would probably go a step further and use a join on the Inventory and warehouse tables to get rid of the sub select. |
|
Quoted:
This should work SELECT Warehouse, AVG(QuantityOnHand) AS AVGquantity FROM INVENTORYWHERE Warehouse IN (SELECT Warehouse FROM WAREHOUSE WHERE Manager='Smith')GROUP BY Warehouse; I would probably go a step further and use a join on the Inventory and warehouse tables to get rid of the sub select. thats the answer, you need the 'group by' since you are 'aggregating' (averaging) QuandityOnHand for each warehouse. select i.warehouse ,avg(i.QuantityOnHand) from inventory i join warehouse w on w.warehouse on i.warehouse and w.manager = 'Smith' group by i.warehouse order by i.warehouse i think thats how you might do it with a join. however the optimizer is likely to do the same thing ETA, i assume your db isnt case sensitive. |
|
right, and sort of
Only one row and 1 column is being returned for the query that works- Smith only has 1 Warehouse (Chicago) and what I need returned is 1 row with 2 colums , Chicago and 217 which is the average qty for all items stored in Smiths warehouse in Chicago |
|
Quoted:
This works: SELECT AVG(QuantityOnHand) AS AVGquantity FROM INVENTORY WHERE Warehouse IN (SELECT Warehouse FROM WAREHOUSE WHERE Manager='Smith'); But this doesnt: SELECT Warehouse, AVG(QuantityOnHand) AS AVGquantity FROM INVENTORY WHERE Warehouse IN (SELECT Warehouse FROM WAREHOUSE WHERE Manager='Smith'); Access gives me error "You tried to execute a query that does not include the specified expression 'Warehouse' as part of an aggregate function" I dont understand why its wrong as Warehouse is a column title in Inventory you are missing the group by in the second query I would write it as follows: select i.Warehouse, avg(QuantityOnHand) AS AVGquantity from INVENTORY i inner join WAREHOUSE w on i.warehouse = w.warehouse where w.manager = 'Smith' group by i.Warehouse |
|
Quoted:
right, and sort of Only one row and 1 column is being returned for the query that works- Smith only has 1 Warehouse (Chicago) and what I need returned is 1 row with 2 colums , Chicago and 217 which is the average qty for all items stored in Smiths warehouse in Chicago That's not going to happen. Because there can be as many warehouses for Smith as you want, and the SQL engine knows this. The average quantity is not necessarily unique to "Chicago". Change it up so you are selecting from both tables, and don't use the sub-query. In the WHERE clause, relate the two with the Warehouse as the foreign key. Then group by warehouse and constrain the manager to be Smith. |
|
Quoted:
Guys, there's only one row that's going to be returned. There's nothing really to group by, because really he's grouping by the manager name. He's looking for the average of that group of warehouses. wrong. he is grouping by warehouse. when you have an aggregate function in the select clause all non aggregates must be in a group by clause |
|
Quoted:
Quoted:
Guys, there's only one row that's going to be returned. There's nothing really to group by, because really he's grouping by the manager name. He's looking for the average of that group of warehouses. wrong. he is grouping by warehouse. when you have an aggregate function in the select clause all non aggregates must be in a group by clause I relooked at it. I stand corrected. What you're saying makes more sense. |
|
Quoted: Quoted: Guys, there's only one row that's going to be returned. There's nothing really to group by, because really he's grouping by the manager name. He's looking for the average of that group of warehouses. wrong. he is grouping by warehouse. when you have an aggregate function in the select clause all non aggregates must be in a group by clause I'm no SQL expert, but I believe that is correct. He needs a group by clause. |
|
OK I see how GROUP BY works. nifty.
someone said something about doing it with a join, if I wanted to do the same thing with a join instead of using a subquery then this kinda works: SELECT INVENTORY.Warehouse, Avg(INVENTORY.QuantityOnHand) AS AVGquantity FROM INVENTORY, WAREHOUSE GROUP BY INVENTORY.Warehouse but I get all the Warehouses, not just Smith's warehouse. If I try: SELECT INVENTORY.Warehouse, Avg(INVENTORY.QuantityOnHand) AS AVGquantity FROM INVENTORY, WAREHOUSE GROUP BY INVENTORY.Warehouse HAVING WAREHOUSE.Manager='Smith'; I get another aggregate function error ETA just figured it out: SELECT INVENTORY.Warehouse, Avg(INVENTORY.QuantityOnHand) AS AVGquantity FROM INVENTORY, WAREHOUSE WHERE INVENTORY.Warehouse=WAREHOUSE.Warehouse and WAREHOUSE.Manager='Smith' GROUP BY INVENTORY.Warehouse; duh. thanks guys |