CODEWITHSUNDEEP
sqlsql-server
codeNinja
codeNinja

Reputation: 1462

select different column if null or empty

I want to select state or the workstate column. Preference is to first use the state value. If its null or empty then i want to use the workstate value.

I have the following sql:

SELECT        
COUNT(id) AS participants, 

COALESCE(NULLIF(state,''), workState, (select state from dbo.training where id =(SELECT top(1) trainingId
    FROM dbo.Xref_Participant_Training
    WHERE (participantId = dbo.Participant.id) )) ) as state

FROM dbo.Participant
WHERE EXISTS
    (SELECT 1
        FROM dbo.Xref_Participant_Training
        WHERE (participantId = dbo.Participant.id) AND (dbo.Participant.country = 'United States'))
GROUP by COALESCE(NULLIF(state,''), workState, (select state from dbo.training where id =(SELECT top(1) trainingId
        FROM dbo.Xref_Participant_Training
        WHERE (participantId = dbo.Participant.id) )) )

I get the following error:

Msg 144, Level 15, State 1, Line 15
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Upvotes: 0

Views: 124

Answers (2)

Squirrel
Squirrel

Reputation: 24803

change the GROUP BY to include the entire expression

SELECT        
       COUNT(id) AS participants, 
       COALESCE(NULLIF(state,''), workState) as state
FROM   dbo.Participant
WHERE EXISTS
    (SELECT participantId
        FROM dbo.Xref_Participant_Training
        WHERE (participantId = dbo.Participant.id) AND (dbo.Participant.country = 'United States'))
GROUP BY COALESCE(NULLIF(state,''), workState)

for your updated query, when the query gets complex, it is easier to use derived table or cte

example syntax for derived table

select col1, col2, count(*)
from
(
    select col1, col2 = coalesce(col2, col3), col4, col5
    from   sometable
) d
group by col1, col2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271141

THIS IS THE ANSWER TO THE ORIGINAL QUESTION.

The issue is that you need to put the entire expression in the group by:

SELECT COUNT(id) AS participants, 
       COALESCE(NULLIF(p.state,''), p.workState) as state
FROM dbo.Participant p
WHERE EXISTS (SELECT 1
              FROM dbo.Xref_Participant_Training pt
              WHERE pt.participantId = p.id
             ) AND
      p.country = 'United States'
GROUP BY COALESCE(NULLIF(p.state, ''), p.workState);

The problem with your query is that GROUP BY state really means GROUP BY p.state. However, you want to aggregate by the column defined in the SELECT.

In addition:

  • Table aliases make the query easier to write and to read.
  • You don't need a column name for an EXISTS subquery.

Upvotes: 2

Related Questions