Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What version of SQL Server please? –  Aaron Bertrand Nov 28 '12 at 17:05
add comment

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
    
still im not getting two decimal places with this code. i changed the sql to fix this: SELECT [date_only] = CONVERT(DATE, InsertedDateTime) ,[Tally] = COUNT() ,avgSize = CONVERT(DECIMAL(10, 2),AVG( size/1024.0/1024) ) ,[daily MB gorwth] = COUNT() * 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; –  kacalapy Nov 28 '12 at 17:32
1  
@kacalapy you're right, I said to perform the cast last, and then I didn't do that. –  Aaron Bertrand Nov 28 '12 at 17:40
add comment

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.

share|improve this answer
    
thanks, i started with the cast to decimal to avoid an overrun of the integer datatype of the size field... my math was failing. –  kacalapy Nov 28 '12 at 17:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.