November 22, 2006 at 9:44 am
I included Count(*) in a query to determine how many records met my criteria. I also had an aggregate variable summing up the values in a column; let's call it TotalQty. So far, so good.
I then wanted to divide TotalQty by the Count to get an average, and display all 3 fields, but I got a syntax error whenever I tried to reference Count in a division calculation.
How can this be done?
Thank you.
November 22, 2006 at 9:46 am
You can't refference the count(*) using the column name. You must do something like this :
Select count(*) AS Cnt, sum(ColName) As Total, sum(ColName) / Count(*) as Average
from...
November 22, 2006 at 10:03 am
I see, thanks. I did have to cast one of the operands to Real in order to prevent truncation, but yes, it did work. Simple enough, huh?
Thank you.
November 22, 2006 at 10:52 am
Yup, but a little counter-intuitive for someone with speed in mind. It does appear that the data is agregated twice when in fact it's done only once .
November 22, 2006 at 11:55 am
I believe you about only aggregating once, but is that something demonstrable or do I have to take it as given?
November 22, 2006 at 12:03 pm
I wouldn't call that a proof but it would be a good start toward it :
SET STATISTICS IO ON
Go
SET STATISTICS TIME ON
GO
SELECT COUNT(*) FROM master.dbo.SysColumns
-- I would assume this to take MUCH more time to compute if the count were done over and over again
-- also you'll notice a 50/50 cost on the execution plans.
SELECT COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*) FROM master.dbo.SysColumns
GO
SET STATISTICS TIME OFF
GO
SET STATISTICS IO OFF
November 27, 2006 at 9:27 am
Just curious...
Any reason not to use the AVG aggregate?
(Is SQL Server smart enough not to compute the Sum twice if it were used? It is still just the one scan but I'm wondering about processing performance.)
Also, if someone has a good article detailing statistics gathering techniques over large databases I'd appreciate a link.
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply