using Count(*)

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

     

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

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

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

  • I believe you about only aggregating once, but is that something demonstrable or do I have to take it as given?

  • 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

  • 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