MYSQL Query using subquery

Question

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 1

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

Answer 2

It was a step in the right direction thanks, I used this.

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