Michael Quiles Michael Quiles - 1 year ago 71
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 Source

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).