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

Larry Lustig · Accepted Answer · 2010-04-28 18:30:53Z

2

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

edited Apr 28, 2010 at 18:30

answered Apr 28, 2010 at 17:12

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Quiles Over a year ago

Added the table to see if this helps

Michael Quiles · Accepted Answer · 2010-04-28 17:31:57Z

0

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;

answered Apr 28, 2010 at 17:31

Michael Quiles

1,1514 gold badges24 silver badges41 bronze badges

1 Comment

Larry Lustig Over a year ago

Actually, you should just remove the SUM around the expression and drop the GROUP BY clause. You're not actually doing any aggregation, so it's misleading to include it. Sorry I missed that the first time.

Collectives™ on Stack Overflow

MYSQL Query using subquery

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related