Came across something I haven't seen before today. . .

  • Not sure I'm expecting an answer from anyone, but I came across something I've not noticed before.  Selecting an aggregate from an empty result set with no group returns NULL whereas I'd expect nothing.  In actuallity, this was hidden in a fairly large process with an error complaining about the attempt to insert a NULL in to a non-NULL column, but I can reproduce it with this simple T-SQL: -
    DECLARE @TABLE AS TABLE
      (
      [ID] TINYINT,
      [GROUPER] CHAR(1)
      );

    -- Returns NULL
    SELECT MAX([ID]) AS [MAX_ID]
    FROM  @TABLE;

    Whereas adding the group by, makes it return "nothing": -
    DECLARE @TABLE AS TABLE
      (
      [ID] TINYINT,
      [GROUPER] CHAR(1)
      );

    -- Returns an empty result set
    SELECT MAX([ID]) AS [MAX_ID]
    FROM  @TABLE
    GROUP BY [GROUPER];

    I'd expect both of the above to return "nothing" as @TABLE is empty.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Since a table represents a set, an empty table represents the empty set { }, so the value of any aggregate is either NULL or zero.

    GROUP BY defines several subsets over which the aggregate should be measured.  The empty set does not have any (proper) subsets, so there are no sets defined by the GROUP BY definition and no sets to calculate aggregates for, so there are no results.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Actually this make sense, since aggregation of at table level should always return a row to represent overall picture of table, you can have count, max, avg etc. in case of no rows in table all records will simply by NULL
    But in case we group data on a column, since there is no data (so there is no group element as well), it will not show any record, because nothing to group. Just imagine if one row has to be displayed with value in all aggregated column as NULL, what will be value in the grouper column, NULL? then logically you will think there is one row in the table where value of grouper is null, which will further mislead.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply