select different column if null or empty

Question

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.

You may want to change the final part of a coalesce to an outer apply instead of a subquery so that the result can be included in the group by. — ZLK
– ZLK, Commented Aug 2, 2017 at 2:22
Take a look at CTEs for Sql Server. You can treat each of your sub-queries as tables, join across the pieces and produce a cleaner statement at the end. — Keith Barrows
– Keith Barrows, Commented Aug 2, 2017 at 4:04

Gordon Linoff · Accepted Answer · 2017-08-02 02:00:35Z

2

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.

edited Aug 2, 2017 at 2:00

answered Aug 2, 2017 at 1:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

codeNinja Over a year ago

ok so what would happen if i had 3 options. See my updated query

Gordon Linoff Over a year ago

@codeNinja . . . Changing the question after it has been answered is rude. It invalidates answers and that can draw downvotes. If you have another question, you should ask another question.

Squirrel · Accepted Answer · 2017-08-02 03:54:04Z

0

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

edited Aug 2, 2017 at 3:54

answered Aug 2, 2017 at 1:40

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

1 Comment

codeNinja Over a year ago

yup that works. I want to extend it a bit further to the final state. See my updated query.

Collectives™ on Stack Overflow

select different column if null or empty

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related