SQL condition checking row elements

Question

I'm really new to sql / stackoverflow in general so bear with me.

I have a distinct select query that returns the following result

Name  Surname  Name2  Surname2  Status
----  -------  -----  --------  ------
Peter  Parker  Mary     Jane       0
Peter  Parker  Felicia  Hardy      1
Peter  Parker  Felicia  Hardy      0
Peter  Parker  Jonah   Jameson     1

So obviously every query will have different names. What I would like to show is if the first person and second person have only one row, if it's 0 id like to show a finished message , if its 1 -> incomplete and if they have 2 rows (0 and 1) I'd like to show Working message.

Name  Surname  Name2  Surname2  Status
----  -------  -----  --------  ------
Peter  Parker  Mary     Jane    Finished
Peter  Parker  Felicia  Hardy   Working
Peter  Parker  Jonah    Jameson Incomplete

How should I go about this one?

Possible duplicate of [Oracle SQL: Update a table with data from another table](https://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table) — Alexandre Elshobokshy, Sep 25 '18 at 08:42
Another possible duplicate : https://stackoverflow.com/questions/49090871/oracle-update-from-select-same-table — Alexandre Elshobokshy, Sep 25 '18 at 08:43
Internet is filled with so many exemples and ways you could try to do this :) They do not answer the question directly but they give good hints — Alexandre Elshobokshy, Sep 25 '18 at 08:43

Zaynul Abadin Tuhin · Accepted Answer · 2018-09-25T09:06:56.670

use case when with aggregate function

    select Name,Surname,Name2,Surname2,
case when count( distinct Status) = 2 then 
         'Working' when sum(Status)=0 then 'Finished' else 'Incomplete'

       end  as status 
  from t
group by Name, Surname, Name2, Surname2;


NAME    SURNAME     NAME2   SURNAME2    STATUS
Peter   Parker      Mary    Jane         Finished
Peter   Parker      Felicia Hardy        Working
Peter   Parker     Jonah    Jameson      Incomplete

Pham X. Bach · Answer 2 · 2018-09-25T08:53:23.397

You could use this

SELECT name, surname, name2, surname2, 
    CASE 
        WHEN COUNT(DISTINCT status) = 2 THEN 'Working'
        WHEN MAX(status) = 1 THEN 'Incomplete' 
        WHEN MAX(status) = 0 THEN 'Finished'
    END AS status
FROM table_name
--WHERE status IN (0, 1)
GROUP BY name, surname, name2, surname2
ORDER BY name, surname, name2, surname2;

Or this:

SELECT name, surname, name2, surname2, 
    CASE
        WHEN MIN(status) = 1 THEN 'Incomplete' 
        WHEN MAX(status) = 0 THEN 'Finished'
        ELSE 'Working'
    END AS status
FROM table_name
WHERE status IN (0, 1)
GROUP BY name, surname, name2, surname2
ORDER BY name, surname, name2, surname2;

If you only have 2 status 0 and 1 then ommit the WHERE clause

Barbaros Özhan · Answer 3 · 2018-09-25T08:57:21.283

0

You might use group by Name, Surname, Name2, Surname2 with avg(status) options

select Name, Surname, Name2, Surname2, 
       ( case when avg(Status) = 1 then 
         'Incomplete'
         when avg(Status) = 0 then
         'Finished'
         else
         'Working'
       end ) as status 
  from tab
group by Name, Surname, Name2, Surname2;

SQL Fiddle Demo

edited Sep 25 '18 at 08:57

answered Sep 25 '18 at 08:50

Barbaros Özhan

59,113
10
31
55

SQL condition checking row elements

3 Answers3