January 21, 2012 at 9:15 pm
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
January 22, 2012 at 4:01 am
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
January 22, 2012 at 11:44 pm
Thanks for the reply. But it does not help.
January 23, 2012 at 12:04 am
Hi ,
I think you are going to have to go into a bit more detail about what you dont understand.
January 23, 2012 at 12:46 am
Thanks Dev and Dave, I got to know what you are saying, It is the result for the NULL values for ProductSubcategoryID.
January 23, 2012 at 7:04 am
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