Michael Quiles Michael Quiles - 1 month ago 17
SQL Question

MYSQL Query using subquery

Cant get this to work can any one help.

List the part number, part description, and on_hand value of each part whose number of units on hand is more than the average number of units onhand for all parts use a subquery?

SELECT PART_NUM,
DESCRIPTION,
SUM(ON_HAND * PRICE) ON_HAND_VALUE
FROM PART;
WHERE MAX(ON_HAND);
(AVG(ON_HAND) > ON_HAND);


Part Table

PART_NUM DESCRIPTION ON_HAND CLASS WAREHOUSE PRICE

AT94 Iron 50 HW 3 24.95
BV06 Home Gym 45 SG 2 794.95
CD52 Microwave Oven 32 AP 1 165.00
DL71 Cordless Drill 21 HW 3 129.95
DR93 Gas Range 8 AP 2 495.00
DW11 Washer 12 AP 3 399.99
FD21 Stand Mixer 22 HW 3 159.95
KL62 Dryer 12 AP 1 349.95
KT03 Dishwasher 8 AP 3 595.00
KV29 Treadmill 9 SG 2 1390.00

Answer

Assuming each part has only one record in the part table (your data structure is not at all clear from the question), this should give you what you want:

 SELECT part_num, description, (on_hand * price) AS on_hand_value
    FROM part
    WHERE on_hand > (SELECT AVG(on_hand) FROM part)

(Based on the data you added to your question, I think this is correct).