CODEWITHSUNDEEP
mysql
saravana
saravana

Reputation: 5

query to count the column data and to display the result with which contains more than 3 times

Table:patient
patient_id  varchar(50)
name  varchar(50)
email_id   varchar(50)
country_id  varchar(50)
mobile_no  varchar(20)

Table:Booking
booking_id  varchar(50)
patient_id  varchar(50)
booking_date  date
booking_status varchar(20)   ---- contains the data: Booked, Cancel
booking_type varchar(20)

booking_id  patient_id      booking_date    booking_status  booking_type
2455          111            21-12-2015         Booked       Via mobile
2424          123            21-12-2015         Cancel       Via web
2455          111            21-12-2015         Cancel       Via Web
2455          111            21-12-2015         Cancel       Via mobile

Patient will book and cancel the appointment with doctor. These will be stored in these tables. Booking status holds the records of Booked and cancelled details.

We want to count the number of bookings and more than 3 cancelled details for each patients.

Expected output:
Name    Email_id       Mobile no    Appointment booked  cancelled
san    [email protected]    235846               2              7
gow    asas@sfsvs      48645151              1              4
abd    ddscz@fsf        489644               4              5

Upvotes: 0

Views: 35

Answers (2)

harshavmb
harshavmb

Reputation: 3902

The below query too works.

select p.name,p.email_id,p.mobileno,
SUM(CASE WHEN b.booking_status='Booked' THEN 1 ELSE 0 END) AS bookedcount,
SUM(CASE WHEN b.booking_status='Cancel' THEN 1 ELSE 0 END) AS cancelcount 
from patient p left join booking b
on p.patient_id=b.patient_id group by p.patient_id,p.email_id,p.mobileno      
having cancelcount>3;

Upvotes: 0

Shadow
Shadow

Reputation: 34294

This is called conditional counting. You can use both count() and sum() with conditional statements as parameters to get the expected output. I'm providing an example for the sum() version below:

select p.patient_id, 
       p.email_id, 
       p.mobile_no,
       sum(if(b.booking_status='Booked',1,0)) as AppointmentBooked,
       sum(if(b.booking_status='Cancel',1,0)) as AppointmentCanceled
from patient p left join booking b on p.patient_id=b.patient_id
group by p.patient_id, p.email_id, p.mobile_no
having AppointmentCanceled>3

Upvotes: 2

Related Questions