sql problem: sum()

[dec21st]

i'm having a problem with my sql below

SELECT 
sum((discounttype.percentage/100)*totalsales.subtotal)
FROM 
totalsales 
INNER JOIN 
discounttype 
ON
totalsales.discounttype = discounttype.discounttypeid 
WHERE
totalsales.invno = 62

the discounttype.percentage is supplse to be 25 and the totalsales.subtotal is suppose to be 250
so that sum that i should get is (25/100)*250 which is 62.5

but the figure i get is 0

why is that so?

discounttype.percentage is INT
totalsales.subtotal is MONEY

why i keep getting 0??? anyway to count this?

 

[DavidT_macktool]

I have used this SQL successfully

Me.SqlSelectCommand1.CommandText = "SELECT DISTINCT Account, SUM(Time) AS TotalTime, SUM(Cash) AS TotalCash, Date FROM [DL Temp] GROUP BY Account, Date"
Me.SqlSelectCommand1.Connection = Me.SqlConnection1

 

[TechGnome]

It's because Percent is integer.....
SQL tries to keep the result in the same data type as the operands....

so in SQL 25 / 100 is 0.... because both 25 and 100 are integers.... but there is a way around it.... Convert it to decimals or money..

CONVERT(money, 25) / CONVERT(money, 100) = .25

So in whole, this is what it will look like:

SELECT 
sum((CONVERT(money, discounttype.percentage)/ CONVERT(money, 100) )*totalsales.subtotal)
FROM 
totalsales 
INNER JOIN 
discounttype 
ON
totalsales.discounttype = discounttype.discounttypeid 
WHERE
totalsales.invno = 62

-- I've assumed Subtotal is already a money datatype... seems to make sense.

Tg

 

[dec21st]

Me.SqlSelectCommand1.CommandText = "SELECT DISTINCT Account, SUM(Time) AS TotalTime, SUM(Cash) AS TotalCash, Date FROM [DL Temp] GROUP BY Account, Date"
Me.SqlSelectCommand1.Connection = Me.SqlConnection1

sorry but i don't quite see the connection from ur post with my question

 

[dec21st]

thanks.. got it working edi