Question 114062 in PostgreSQL Question posted in
The official documentation can be found here.

removing duplicate values and null postgresql

with res as(select us.column,mg.column as status,count(mg.column)  FROM Table_name1 it
LEFT JOIN   Table_name2 us on it.column=us.column
LEFT JOIN   Table_name3 mg on it.column=mg.column
where it.column   is not null and it.column in(5,6) and (it.column + '05:30:00'::INTERVAL)::date between '2022-08-28' and '2022-10-03'  group by us.column,mg.column)
select res.column,
(case when column='Open' then count end) as Open_status,
(case when column='Closed' then count end) as Closed_status
from res  group by res.column,res.column,res.column

i need to solve the remove duplicate entry in column1 open and close when it open and close status count details show and when it close the open details show the particular person

i got output like this

expected output like this

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    SELECT table1.first_name,
         table1.last_name,
         table2.open_status,
         (CASE WHEN CONCAT(table1.first_name, table1.last_name) = '' 
               THEN table2.open_status 
               ELSE CONCAT_WS(' ', max(table1.open_status), max(table1.closed_status)) 
          END) AS Status,
FROM     table1
JOIN     table2 ON table1 = table2
JOIN     table3 ON table2= table3
where table.column   is not null and table.column(condition) and (table.column + '05:30:00'::INTERVAL)::date between '2022-08-28' and '2022-10-03')
GROUP BY table1.first_name,
         table1.last_name,
         table2.copmany_name,
         (CASE WHEN CONCAT(table1.first_name, table1.last_name) = '' 
               THEN table2.open_status 
               ELSE CONCAT_WS(' ', table1.first_name, table1.last_name) 
          END) AS name

  2. 0 
    -- Make new complete entries from any incomplete entries
INSERT INTO duplicated
     SELECT first_name
          , max(open_status)
          , max(closed_status)
       FROM duplicated
      WHERE open_status IS NULL OR closed_status IS NULL
      GROUP BY first_name
;
-- Remove the incomplete entries leaving only complete entries
DELETE
  FROM duplicated
 WHERE open_status IS NULL OR closed_status IS NULL
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search