Calculation Query Problem 1

[alexanderthegreat]

Hi, how come I am getting the wrong subtotal in the following view? My results are underneith it

SELECT COALESCE (qty1, 0) * COALESCE (priceeach1, 0) AS dollars1, COALESCE (qty2, 0) * COALESCE (priceeach2, 0) AS dollars2, COALESCE (qty3, 0) * COALESCE (priceeach3, 0) AS dollars3, COALESCE (qty4, 0) * COALESCE (priceeach3, 0) AS dollars4, COALESCE (qty5, 0) * COALESCE (priceeach5, 0)
AS dollars5, COALESCE (dollars1, 0) + COALESCE (dollars2, 0) + COALESCE (dollars3, 0) + COALESCE (dollars4, 0) + COALESCE (dollars5, 0)
AS Subtotal
FROM dbo.RFQ

dollars1 etc Subtotal
86.9000 22.5000 .0000 .0000 .0000 .0000
39.5000 2.7500 .0000 .0000 .0000 257.0000
1.6500 28.5000 .0000 .0000 .0000 330.0000
7.5000 1.2500 .0000 .0000 .0000 301.0000

 

[SQLDenis]

shouldn't this
COALESCE (qty4, 0) * COALESCE (priceeach3, 0) AS dollars4
be this?
COALESCE (qty4, 0) * COALESCE (priceeach4, 0) AS dollars4

and in the end to get the subtotal you have to do

COALESCE (qty1, 0) * COALESCE (priceeach1, 0) +
COALESCE (qty2, 0) * COALESCE (priceeach2, 0) +
COALESCE (qty3, 0) * COALESCE (priceeach3, 0) +
COALESCE (qty4, 0) * COALESCE (priceeach4, 0) +
COALESCE (qty5, 0) * COALESCE (priceeach5, 0)
AS dollars5


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[alexanderthegreat]

Thanks Denis

yes about your first inquiry and for the seond part dollars5 should be subtotal correct?

 

[SQLDenis]

yes that's correct dollars5 should be subtotal , my bad

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[alexanderthegreat]

Your the best adn your query is very sleek compared to my original bulky one.

Thanks very much.

al