Sql-server – how to get avg() size by day using two decimal places

sql server

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?

Best Answer

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.)

Related Question