count visit as 1 with the same doctor, id and visitdate

• hbanerje

  SSC Eights!

  Points: 818

  More actions

  January 31, 2011 at 8:27 pm

  #233029

  for example i have table where I have

  id doctor visit date patient medicine

  111 dr.Smith 01/01/2010 Mr. Jerry 123

  111 dr.Smith 01/01/2010 Mr.Jerry 456

  111 dr.smith 01/01/2010 Mr.Jerry 678

  I want to count the visit

  where visit = id + doctor + visitdate ( for the same of these three).

  How to write this query?

  thanks,

  Hai

• nandakrishnacherukuri

  SSC Enthusiast

  Points: 131

  More actions

  January 31, 2011 at 8:57 pm

  #1280726

  Hi,

  create table dc(id int,doctor_name varchar(20),date datetime,patient_name varchar(20),medicine int)

  insert into dc values(111,'dr.Smith','01/01/2010','Mr.Jerry',123)

  insert into dc values(111,'dr.Smith','01/01/2010','Mr.Jerry',456)

  insert into dc values(111,'dr.smith','01/01/2010','Mr.Jerry',678)

  insert into dc values(112,'dr.smith','01/02/2010','Mr.kk',999)

  insert into dc values(112,'dr.nn','02/02/2010','mr.kk',88)

  select * from dc

  select id,count(*) as cnt from dc

  group by id,doctor_name,date

• hbanerje

  SSC Eights!

  Points: 818

  More actions

  February 1, 2011 at 7:39 am

  #1280929

  Hi,

  I tried that but I am getting 3 visits in that case, which is not right, I should be getting only one visit because visit =1 for the same id, doctor and the date.

  Please help.

  Thanks,

  Hai

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  February 1, 2011 at 7:47 am

  #1280934

  in that case why do you need a count as you will be grouping by distinct rows so the count will always be one (?)

  select id,doctor_name,date, 1 as cnt from dc

  group by id,doctor_name,date

• hbanerje

  SSC Eights!

  Points: 818

  More actions

  February 2, 2011 at 7:30 pm

  #1281697

  Thank you very much. It works.