problem w/ GROUP BY 1

[rds80]

My data is:

ControlID[tab]NoteID[tab]PropertyID[tab]Month[tab]State
06-0007[tab]9[tab]20110[tab]January[tab]TX
00-0290[tab]19976[tab]21134[tab]January[tab]PA
00-0290[tab]19976[tab]21234[tab]January[tab]NJ
00-0290[tab]19976[tab]21456[tab]January[tab]FL
00-0212[tab]20162[tab]21635[tab]January[tab]NY
00-0212[tab]20162[tab]21636[tab]January[tab]NY
00-0212[tab]20144[tab]21635[tab]January[tab]NY
00-0212[tab]20144[tab]21636[tab]January[tab]NY

This is the sql query that I have right now:

SELECT CM.ControlID, N.NoteID, DATENAME(m, N.NoteDate) AS MONTH,
CASE WHEN COUNT(P.State) > 1 THEN 'Various' END
FROM tblControlMaster Cm
INNER JOIN tblNote N
    ON N.ControlID_F = CM.ControlID
LEfT JOIN tblProperty P
    ON P.ControlID_F = Cm.ControlID
WHERE CM.LoanStatusCD_F In ('Z', 'G')
GROUP BY CM.ControlID, N.NoteID, N.NoteDate

My result set shows:

ControlID  NoteID       Month       State
06-0007	   9	        January	    NULL
00-0290	   19976	January	    Various
00-0212	   20162	January	    Various
00-0212	   20144	January	    Various
00-0212	   20148	January	    Various

I need the output to be:

ControlID  NoteID       Month       State
06-0007	   9	        January	    NULL
00-0290	   19976	January	    Various
00-0212	   20162	January	    NY
00-0212	   20144	January	    NY
00-0212	   20148	January	    NY

I've understood why I'm not getting the correct output, but now I'm trying to figure out how to get the correct output. Please help.

 

[alicorn2]

I haven't tested this but could you change

CASE WHEN COUNT(P.State) > 1 THEN 'Various' END

to something like:

CASE WHEN MIN(P.State) <> MAX(P.State) THEN 'Various' ELSE MIN(P.State) END

 

[rds80]

Thank you so much alicorn2...It works
If you could please explain the logic behind that.

 

[sparkbyte]

Logic

CASE WHEN MIN(P.State) <> MAX(P.State) THEN 'Various' ELSE MIN(P.State) END

When Minimum 'State' NOT Maximum 'State' Then 'Various' ....

If there is more than one state then set the value to various.

Hope this helps.

Thanks

John Fuhrman
Titan Global Services

http://www.titangs.com