CODEWITHSUNDEEP
mysql
Naveen
Naveen

Reputation: 77

How to avoid duplicates using union with group by clause in mysql

(select bm.branch_name, u.username, count(pv.visit_id) as walkin, null as app
 from user u inner join doctor_detail dd
     on dd.user_id = u.user_id
 inner join branch_master bm
     on bm.branch_id = dd.branch_id
 inner join patient_visit pv
     on pv.doctor_detail_id = dd.doctor_detail_id and
        pv.created_at = DATE_FORMAT(now(), '%Y-%b-%d')
 group by dd.doctor_detail_id)
union all
(select bm.branch_name, u.username, null as walkin, count(a.appointment_id) as app
 from user u inner join doctor_detail dd
     on u.user_id = dd.user_id
 inner join appointment a
     on a.doctor_id = dd.doctor_detail_id
 inner join branch_master bm
     on bm.branch_id = a.branch_id and
        a.dov = DATE_FORMAT(now(), '%m/%d/%Y')
 group by dd.doctor_detail_id);

The above query returns the output like

|branch_name |  username            |walkin | app  |
|------------|----------------------|-------|------|
|Tnagar      |  Ganesh              | 3     | null | 
|Tnagar      |  Ganesh              | null  |  2   |  
|Tnagar      |  Chidambara Bharathi | null  |  1   |
|T.Nagar     |  Krishnan            | null  |  1   |

But I want the output looks like

|branch_name |  username            |walkin | app  |
|------------|----------------------|-------|------|
|Tnagar      |  Ganesh              |  3    |  2   |  
|Tnagar      |  Chidambara Bharathi |  0    |  1   |
|T.Nagar     |  Krishnan            |  0    |  1   |

How to avoid duplicates using union with group by clause. Please help me to solve this problem.Thank you.

Upvotes: 1

Views: 77

Answers (1)

Barmar
Barmar

Reputation: 780769

You can combine the related rows using GROUP BY.

SELECT branch_name, username, MAX(walkin) AS walkin, MAX(app) AS app
FROM ((select bm.branch_name, u.username, count(pv.visit_id) as walkin, null as app
         from user u inner join doctor_detail dd
             on dd.user_id = u.user_id
         inner join branch_master bm
             on bm.branch_id = dd.branch_id
         inner join patient_visit pv
             on pv.doctor_detail_id = dd.doctor_detail_id and
                pv.created_at = DATE_FORMAT(now(), '%Y-%b-%d')
         group by dd.doctor_detail_id)
        union all
        (select bm.branch_name, u.username, null as walkin, count(a.appointment_id) as app
         from user u inner join doctor_detail dd
             on u.user_id = dd.user_id
         inner join appointment a
             on a.doctor_id = dd.doctor_detail_id
         inner join branch_master bm
             on bm.branch_id = a.branch_id and
                a.dov = DATE_FORMAT(now(), '%m/%d/%Y')
         group by dd.doctor_detail_id)
) AS x
GROUP BY branch_name, username

Upvotes: 2

Related Questions