question

Sqeakwool avatar image
Sqeakwool asked

why does this version work for my procedure but if i add the customer.* in select i get an error

DELIMITER //


CREATE PROCEDURE sp_avg_price (IN city_x Varchar(50))

BEGIN

SELECT AVG(product.productPrice)

FROM customer, orders, orderLine, product

WHERE customer.custID = orders.custID

AND orderline.orderID = orders.orderID

AND orderline.productID = product.productID

AND customer.city = city_x;

END //


Delimiter ;


CALL sp_avg_price('SLC');


sql databaselets-reward-matt-somehowunknown
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered

The mostly likely option is that AVG, like many other functions, works over a group of results. In the above example, the group is "everything". When you put in "customer.*" in the select list, but don't provide a GROUP BY clause, SQL server doesn't know what grouping to average over.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.