sql problem: sum()

dec21st

Active member
Joined
May 14, 2005
Messages
43
Programming Experience
3-5
i'm having a problem with my sql below

VB.NET:
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?
 
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
 
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:

VB.NET:
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
 
DavidT_macktool said:
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
 
Back
Top