SQL Server Performance

Aggregate Functions

Discussion in 'Performance-Related Article Discussions' started by shanetasker, Feb 4, 2006.

  1. shanetasker New Member

  2. mmarovic Active Member

    Good beginners article, the only thing missing is count(*) is not mentioned and difference between count(*) and count(column).
  3. FrankKalis Moderator

    In SQL Server 2005, you can implement your own aggregate function. Might have been worth mentioning. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
  4. jakenm New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />In SQL Server 2005, you can implement your own aggregate function. Might have been worth mentioning. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />That's an excellent suggestion - especially because, although they're listed in this article, FIRST() and LAST() don't exist in SQL Server. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />-Jake
  5. FrankKalis Moderator

    Well, FIRST() and LAST() might not be the "best" examples. I, for one, would love to have a PROD() function that multiplies the values of a column. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  6. Madhivanan Moderator

    shanetasker, Did you successfully executed First and Last function? Only Access support them and not SQL Server [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  7. FrankKalis Moderator

    Madhivanan, Shane is *not* the author of that article. Read his autosig. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  8. Madhivanan Moderator

    Well. It will be nice If author name is specified in the Article [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  9. jmoch New Member

    Hello, J've just red the article. It was stated previously that this is a good article for beginners.
    I cannot agree with it. It contains too much mistakes.
    See below two ones not mentioned in discussion in our forum.
    Example1:"
    If you wanted to select a mobile phone of type "GSM," you could write the following query.SELECT model FROM Mobile_phones WHERE make =
    (SELECT make FROM Mobile_phones WHERE type = 'GSM')" I am against it because:
    1. It will not work, (you have to replace first "=" with "in").
    2. Such ugly code should not be presented for beginners.
    3. To select the mobile phones of "GSM" type I suggest to write the following (assuming that model alone indicates the phone):
      SELECT model FROM Mobile_phones WHERE type = 'GSM'
    Example2:"SELECT Type, Make, COUNT(Type) AS Tot_Num
    FROM Mobiles_phones
    GROUP BY Type, Make This query gives you the following result.
    TypeModelTot_Num
    ----------------
    GSMNokia3
    CDMANokia2
    3GNokia2
    "Two mistakes:
    1. "Make" from select statement somehow changed into "Model" in result.
    2. To have output limited to Nokia one have to use the where clause:
      WHERE Make = 'Nokia'
    Regards,Jaroslaw Moch
    P.S.Personally, I prefer keeping to one naming convention (in spite of the fact that MS SQL is not case sensitive) and not use "Model" and "model" referring to the same column name.
  10. satya Moderator

    Appreciate your feedback, if permits we may ask that article author to include these as well. If not it is nothing but that is applicable to previous versions of SQL.
  11. Madhivanan Moderator

    <P mce_keep="true">[quote user="FrankKalis"]In SQL Server 2005, you can implement your own aggregate function. Might have been worth mentioning. [<IMG alt=:) src="http://sql-server-performance.com/community/emoticons/emotion-1.gif">]<BR><BR>--<BR>Frank Kalis<BR>Microsoft SQL Server MVP<BR <a href="http://www.insidesql.de" target="_blank">http://www.insidesql.de</A><BR>Heute schon gebloggt?<A href="http://www.insidesql.de/blogs" target=_blank>http://www.insidesql.de/blogs</A><BR>[/quote]</P><P mce_keep="true">&nbsp;</P><P>I think I dont understand what you meant by In SQL Server 2005, you can implement your own aggregate function [:)]</P>

Share This Page