MYSQL Query using SUM

Question

How can I make this select the 7500 or greater from the ON_HAND VALUE column?

" List the part number part descrtiption and onhand value for each part whose on hand value is at least $7,500. Assign ON_HAND_VALUES as the computed column."

This is what I have so far, I tried subquerys IN statements but I cant figure it out.

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

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

knittl · Accepted Answer · 2010-04-27 18:05:25Z

2

you can't use where, you have to use having with aggregate functions:

SELECT PART_NUM, DESCRIPTION, SUM(ON_HAND * PRICE) AS ON_HAND_VALUE
FROM PART
GROUP BY PART_NUM, DESCRIPTION;
HAVING SUM(ON_HAND * PRICE)'7500'

where is evaluated before grouping and summing happens, having evaluates on the result set, after records are filtered and grouped

answered Apr 27, 2010 at 18:05

knittl

269k59 gold badges339 silver badges405 bronze badges

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

Comments

Michael Quiles · Accepted Answer · 2010-04-27 18:38:26Z

0

This is what I used.

SELECT PART_NUM, DESCRIPTION, SUM(ON_HAND * PRICE) AS ON_HAND_VALUE
FROM PART
GROUP BY PART_NUM
HAVING SUM(ON_HAND * PRICE)  > '7500'
ORDER BY PART_NUM;

answered Apr 27, 2010 at 18:38

Michael Quiles

1,1514 gold badges24 silver badges41 bronze badges

Comments

Klaster_1 Нет войне · Accepted Answer · 2014-02-10 06:58:17Z

0

The first two people are wrong. I know for a fact this is correct:

SELECT PART_NUM, DESCRIPTION, (ON_HAND * PRICE) AS ON_HAND_VALUE
FROM PART
WHERE (ON_HAND * PRICE) >= 7500

edited Feb 10, 2014 at 6:58

Klaster_1 Нет войне

12.2k9 gold badges65 silver badges75 bronze badges

answered Feb 10, 2014 at 6:38

user3291711

1

Collectives™ on Stack Overflow

MYSQL Query using SUM

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related