SQL SERVER query field sizes

[Colm Kavanagh]

Following query is returning 0 value for the average even though there actual value should be 5/8. The field size of underlying table is int. How can I get my query to return a real value rather than an integer?

DECLARE @ContextDate Date;

set @ContextDate = CONVERT(DATETIME, '2012-08-17', 102);

SELECT [Client-id], [Client-name], SUM([SumOfCreated transactions (number)]) AS [Sum Created transactions (number)], AVG([SumOfCreated transactions (number)])
AS [Avg Created transactions (number)], AVG([SumOfCreated refunds (number)]) AS [Avg Created refunds (number)], AVG([Created transactions (EUR value)])
AS [Avg Created transactions (EUR value)], SUM([Created transactions (EUR value)]) AS [Created transactions (EUR value)], SUM([Net Created (EUR value)])
AS [Net Created (EUR value)]
FROM dbo.Stats_Batch_Period_a(@ContextDate) AS Stats_Batch_Period_a_1
GROUP BY [Client-id], [Client-name]

[Erland Sommarskog]

Post by Colm Kavanagh
Following query is returning 0 value for the average even though there
actual value should be 5/8. The field size of underlying table is int.
How can I get my query to return a real value rather than an integer?

By converting the column to the data type you want the result in.

[Colm Kavanagh]

Do you mean for the original table the query is based on or is there some way to set the field for the resulting result field?

[rpresser]

Post by Colm Kavanagh
Following query is returning 0 value for the average even though there actual value should be 5/8. The field size of underlying table is int. How can I get my query to return a real value rather than an integer?

Instead of

AVG([SumOfCreated transactions (number)])

use

AVG(convert(float, [SumOfCreated transactions (number)]))

and similarly for other averages.

[Colm Kavanagh]

makes sense. thanks