CODEWITHSUNDEEP
sql-server
MoiD101
MoiD101

Reputation: 195

Selecting Data from a column where NULLs and strings exist

Hi i have a query that appears to be pulling in 'Not Set' twice:

enter image description here

Now after a few checks i know it is because the currentstage column has NULLS and the string 'Not Set'stored in the table. So below yields 3 strings of 'Not set' and 195 NULLS forced to 'Not Set'. But what i actually want to see is 198 x 'Not Sets' in my #TempCircCount. How can this be done please?

My Failing code is here:

IF OBJECT_ID('tempdb..#TempCircCount') is not null
DROP TABLE #TempCircCount
SELECT 
    ISNULL(cirRep.CurrentStage, 'Not Set') AS CurrentStage,
    COUNT(ISNULL(cirRep.CurrentStage, 'Not Set')) AS Circuits
INTO #TempCircCount
FROM 
    [QuoteBase].[dbo].[CircuitReports] cirRep
RIGHT JOIN 
    Quotebase.dbo.Circuits cir ON cir.[PW Number] = CirRep.[PWNumber]
WHERE 
    Cir.Status='New Circuit Order'
GROUP BY CurrentStage 
ORDER BY CurrentStage

SELECT 
    ISNULL(CurrentStage, 'Not Set') AS [CurrentStage], 
    Circuits AS Circuits
FROM #TempCircCount
GROUP BY CurrentStage,  Circuits
ORDER BY CurrentStage

Upvotes: 0

Views: 45

Answers (1)

Bernhard Barker
Bernhard Barker

Reputation: 55649

I believe simply changing

GROUP BY CurrentStage

to

GROUP BY ISNULL(cirRep.CurrentStage, 'Not Set')

would work.

The GROUP BY uses CurrentStage from one of your table fields (i.e. cirRep.CurrentStage) instead of the field in the select. SQL server disallows grouping by a field in the select.

I also recommend not using the same names for your output fields as already existing fields for exactly this reason.

Upvotes: 3

Related Questions