Adventure Works R2 Query Result Help

  • I am executing this query in AdventureWorks2008R2 database

    SELECT Production.Product.ProductSubcategoryID

    , AVG(Listprice) AS 'Average'

    , MIN(Listprice) AS 'Minimum'

    , MAX(Listprice) AS 'Maximum'

    FROM Production.Product

    WHERE ListPrice <> 0

    GROUP BY Product.ProductSubcategoryID

    The first row in the resultset is below. Please help me identify how this result is coming.

    ProductSubcategoryIDAverageMinimumMaximum

    NULL 159.1333133.34196.92

  • select *

    from production.product

    where productsubcategoryid is null

    ProductSubcategoryID has NULL values in Production.Product table.

    Per BOL:

    Null values are treated as the lowest possible values.

    For More: http://msdn.microsoft.com/en-us/library/ms188385.aspx

  • Thanks for the reply. But it does not help.

  • Hi ,

    I think you are going to have to go into a bit more detail about what you dont understand.



    Clear Sky SQL
    My Blog[/url]

  • Thanks Dev and Dave, I got to know what you are saying, It is the result for the NULL values for ProductSubcategoryID.

  • Please read my post again.

    Run the given query, you will find there are NULL values in productsubcategoryid.

    Your query is grouping the data based on productsubcategoryid thus there is a group of NULL value as well. Grouping internally sorts (default ascending order) the results thus I added a comment indicating NULL is considered as lowest possible value.

    For more information I have given the BOL reference as well.

    Please try different permutations for grouping, you will understand how it works.

    If a grouping column contains null values, all null values are considered equal, and they are put into a single group.

    For More: http://msdn.microsoft.com/en-us/library/ms177673.aspx

Viewing 6 posts - 1 through 5 (of 5 total)

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