September 10, 2011 at 12:03 am
Hi,
this sql code returns rows where an id may have more than one row. I want to find out how many id's have more than one row and by how many. It's early and I'm fairly new to sql so any help would be appreciated...and an explanation if your feeling generous 🙂
select patient.dbpatcnt as id,
patient.dbpatfirstname as fname,
patient.dbpatlastname as lname,
phone.dbphonenumber as phnNum,
phone.dbphonetypeid as phnTypeID
FROM PATIENT
inner join lnkphone on lnkphone.dbkeycnt = patient.dbpatcnt
inner join phone on phone.dbphoneid = lnkphone.dbphoneid
where patient.dbstatusid <> 13
and phone.dbphonetypeid in (5,9)
order by patient.dbpatcnt
I'm more interested in the count of how many id's have more than one row than th e id's themselves but both would be good.
September 10, 2011 at 1:58 am
Something like this?
select patient.dbpatcnt as id,
COUNT(*) as cnt,
COUNT(patient.dbpatcnt) OVER (PARTITION BY (SELECT 1)) as total_cnt -- works only on SQL 2005 and above
FROM PATIENT
inner join lnkphone on lnkphone.dbkeycnt = patient.dbpatcnt
inner join phone on phone.dbphoneid = lnkphone.dbphoneid
where patient.dbstatusid <> 13
and phone.dbphonetypeid in (5,9)
GROUP by patient.dbpatcnt
HAVING COUNT(*)>1
order by patient.dbpatcnt
September 10, 2011 at 5:39 am
Thanks that works...I'm giving you a virtual pat on the back!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply