Post reply

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

  • 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

  • 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

  • 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

  • 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

  • February 2, 2011 at 7:30 pm

    #1281697

    Thank you very much. It works.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply