Post reply

using Count(*)

  • November 22, 2006 at 9:44 am

    #175465

    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

    #673509

    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

    #673518

    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

    #673533

    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

    #673554

    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

    #673556

    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

    #674099

    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