Null Values.........

[GMFTM]

I have a query that has group by's and an expresstion and 4 calculated columns. In the calculated colums I have NULLS that are returned. In these three columns I do not want to include them in the dataset if all three calculated fields are NULL or 0.

I have posted the SQL below:

SELECT [Auction Data].AuctionName,
[Auction Data].Company,
[Auction Data].Month,
[Auction Data].MY,
Count([Auction Data].Offered) AS CountOfOffered,

IIf(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered])<Avg([211 Offered Sold tDevMY]. SRStDevMin]),Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered]),
IIf(Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered])>Avg([211 Offered Sold StDev MY].[SRStDevMax]),Sum([Auction Data].[Sale Rate])/Count([Auction Data].[Offered]),Null)) AS [Sale Rate],

IIf(([Auction Data].[Sale_Price])<([211 Offered Sold StDev MY].[SPStDevMin]),([Auction Data].[Sale_Price]),
IIf((([Auction Data].[Sale_Price])>([211 Offered Sold StDev MY].[SPStDevMax])),([Auction Data].[Sale_Price]))) AS [Sale Price],

IIf(([Auction Data].[Days to Sell])<([211 Offered Sold StDev MY].[DTSStDevMin]),([Auction Data].[Days to Sell]),
IIf((([Auction Data].[Days to Sell])>([211 Offered Sold StDev MY].[DTSStDevMax])),([Auction Data].[Days to Sell]))) AS [Days to Sell]

FROM [Auction Data]
LEFT JOIN [211 Offered Sold StDev MY] ON ([Auction Data].MY = [211 Offered Sold StDev MY].MY)
AND ([Auction Data].Month = [211 Offered Sold StDev MY].Month)
AND ([Auction Data].Company = [211 Offered Sold StDev MY].Company)

GROUP BY [Auction Data].AuctionName,
[Auction Data].Company,
[Auction Data].Month,
[Auction Data].MY,
IIf(([Auction Data].[Sale_Price])<([211 Offered Sold StDev MY].[SPStDevMin]),([AuctionData].[Sale_Price]),
IIf((([Auction Data].[Sale_Price])>([211 Offered Sold StDev MY].[SPStDevMax])),([Auction Data].[Sale_Price]))),
IIf(([Auction Data].[Days to Sell])<([211 Offered Sold StDev MY].[DTSStDevMin]),([Auction Data].[Days to Sell]),
IIf((([Auction Data].[Days to Sell])>([211 Offered Sold StDev MY].[DTSStDevMax])),([Auction Data].[Days to Sell])));

 

[xenou]

Hi,
you can write a second query to select from this first query:

Select * from MyQuery 
WHERE NOT (
    [Sale Rate] is null AND
    [Sale Price] is null AND 
    [Days to Sell] is null
    )