how to get avg() size by day using two decimal places

Question

does this simple query result in grouping the attachments by day and giving me the total number of daily attachments as well as the average size?

the only table i am querying is attachmentdetail (email attachments)

[attachmentdetail]
    AttachmentId     int
    Name             varchar
    Size             int
    IsInline     bit
    InsertedDatetime datetime

select 
    count(*) as [Tally], 
    avg( cast(size as decimal)/1024/1024) as avgSize,
    DateAdd(dd, DateDiff(dd, 0, InsertedDatetime), 0) As [date_only]  
from attachmentdetail 
where isinline <>1
group by DateAdd(dd, DateDiff(dd, 0, InsertedDatetime), 0)
order by [date_only] desc

also how to i keep the values i get from my avg() to 2 decimal places?

Answer 1

You should specify precision and scale when casting to a decimal, and perform that cast last. This blog post explains why you never want to leave out the size declaration for char/varchar/nchar/nvarchar, but similar arguments hold for decimal and a few other numeric types, too.

Also, on 2008+, CONVERT(DATE is a slightly more efficient (and easier to read) method of stripping time (see this post and this follow-up).

Here is how I would write your query:

SELECT
  [date_only] = CONVERT(DATE, InsertedDateTime),
  [Tally] = COUNT(*),
  avgSize = CONVERT(DECIMAL(10, 2), AVG(size/1024.0/1024))
FROM dbo.attachmentdetail 
WHERE isinline <> 1
GROUP BY CONVERT(DATE, InsertedDateTime)
ORDER BY [date_only] DESC;

(Also, use schema prefixes and statement terminators. Always.)

Answer 2

If you want only two decimal places in the average, you can CAST to a DECIMAL type limting the precision, like this:

CAST( avg( cast(size as decimal)/1024/1024) AS DECIMAL(6,2)) as avgSize

Looks like you're casting "size" to decimal just to get the math to work as decimal instead of integers, where you might round too much. It doesn't matter much, but I prefer to do that by specifying a decimal constant because I think it's easier to read. I'd re-write that column like this:

CAST( AVG( size / 1024.0 / 1024.0 ) AS DECIMAL(6,2)) AS avgSize

If you're using a recent version of SQL Server, you might have a simpler way to create your date_only column, as well. You can just cast the DATETIME value to a DATE type, which doesn't include any time information:

CAST( InsertedDateTime AS DATE ) AS [date_only]

Other than that, I think your query does what you say you expect.