Multiple calculations in select statement 1

[LloydDobler]

Hi everyone, having a little trouble with what I thought would be a simple calculation. I need to group the following by MachineNumber. What I need to do is add the totalScrap & totalPRD, and divide totalScrap into the totalPRD for each machine to give us PPM. Below is my statement, some values returned are correct, but others are off by quite a bit and I don't understand why. Hope someone can help.

SELECT
machinenumber, sum((ScrQnty1 + ScrQnty2 + ScrQnty3 + ScrQnty4) /(QuantityProduced + ScrQnty1 + ScrQnty2 + ScrQnty3 + ScrQnty4)*1000000) as PPM

FROM tbl_Production

WHERE
((QuantityProduced + ScrQnty1 + ScrQnty2 + ScrQnty3 + ScrQnty4) > 0) AND (PRDdate BETWEEN '01/01/04' AND '01/31/04')

GROUP BY MachineNumber
ORDER BY MachineNumber

 

[SQLSister]

What are your data types?

 

[SQLBill]

You say some of the data is 'off'. Find one that you consider 'off', retrieve the data from the database and compute it by hand. There could be bad data in the row.

-SQLBill

 

[LloydDobler]

All data types are numeric. As for the bad data, if i use the select statement below, and compute it by hand, I get different (but correct) results than i do with my previous statement.

 

[SQLBill]

What 'select statement below'????

-SQLBill

 

[SQLBill]

Can you give us a sample of the data? At least one row that works and one row that doesn't.

-SQLBill

 

[LloydDobler]

oops, sorry, this select statement:

SELECT
machinenumber, sum(ScrQnty1 + ScrQnty2 + ScrQnty3 + ScrQnty4) as TotalScrap, sum(QuantityProduced + ScrQnty1 + ScrQnty2 + ScrQnty3 + ScrQnty4) as TotalPRD

FROM tbl_Production
WHERE
(PRDdate BETWEEN '01/01/04' AND '01/31/04') AND(QuantityProduced + ScrQnty1 + ScrQnty2 + ScrQnty3 + ScrQnty4) > 0

GROUP BY MachineNumber
ORDER BY MachineNumber

This will output the following:

Mach# TotalScrap TotalPRD
00-042 7 328
00-002 166 152569
00-004 389 69592


Whereas the first SELECT statement I used in this post would return the results:

Mach# PPM
00-042 21341.46
00-002 1166723.28
00-004 997038.29

00-042 works out fine in both, but the other 2 are messed up!

 

[SQLBill]

In the first statement you have this:

QuantityProduced + ScrQnty1 + ScrQnty2 + ScrQnty3 + ScrQnty4

but in the second statement, you have this:

sum(QuantityProduced + ScrQnty1 + ScrQnty2 + ScrQnty3 + ScrQnty4)

Notice the SUM in the second statement is missing in the first.


Try this and see what you get: (copy it as is, I added a sum and made parenthesis changes.)

SELECT
machinenumber, (sum(ScrQnty1 + ScrQnty2 + ScrQnty3 + ScrQnty4) /sum(QuantityProduced + ScrQnty1 + ScrQnty2 + ScrQnty3 + ScrQnty4))*1000000 as PPM


-SQLBill

 

[LloydDobler]

Thanks SQLBill, that did the trick. I didn't realize a second SUM statement was needed. I guess some values were 0 anyway, which is why it would return some results properly.