CODEWITHSUNDEEP
mysqlsql
Maddie Graham
Maddie Graham

Reputation: 2177

SQL - case that returns repeated values ​in the table

I am trying to get 'more' in the sql query for all values ​​repeated in the table for the 'CE.TITLE' column. I made a short CASE but it ends with a mistake.

CASE
    WHEN having count(CE.TITLE) > 1
    THEN 'More'
    ELSE 'ok'
END as more than one job

Error:
ORA-0093: missing expression
0036. 0000 -  "missing expression"
*Cause:    
*Action:
Error at Line: 46 Column: 10

Any help will be appreciated

Upvotes: 0

Views: 84

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

The having is not appropriate:

(CASE WHEN count(CE.TITLE) > 1
      THEN 'More'
      ELSE 'ok'
 END) as more_than_one_job

This fixes the syntax error and assumes the overall query is an aggregation query. If not, you can use window functions (in MySQL 8+). I am guessing the logic you really want is:

(CASE WHEN count(*) over (partition by CE.TITLE) > 1
      THEN 'More'
      ELSE 'ok'
 END) as more_than_one_job

In earlier versions, you can use a correlated subquery. My preference would be:

(CASE WHEN EXISTS (SELECT 1
                   FROM ce ce2
                   WHERE ce2.title = ce.title AND
                         ce2.id <> ce.id
                  )
      THEN 'More'
      ELSE 'ok'
 END) as more_than_one_job

Upvotes: 2

Related Questions