GROUP BY: How to COUNT() records with a set of field values? 2

[ProtocolPirate]

I have labels in a certain field such as MRI, MRIWO and MRIWWO, and I want to be able to count the total number of occurances of records with all three values for a given doctor.

 

[PHV]

How do you may have all three values in a record ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

SELECT Label, Count(*) FROM TableName
WHERE Label IN ("MRI", "MRIWO", "MRIWWO") and DoctorID = 1
GROUP BY Label

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[ProtocolPirate]

That will give me one label, but I have six or eight groupings of a total of 50 different labels. Things will proliferate out of control quickly using this method.

 

[ProtocolPirate]

How do you may have all three values in a record ?"

No, there is only one value per record for this field.

 

[PHV]

So, any chance you could CLEARLY explain what you want to do ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

So, I'm expecting that you have:
[tt]
SomeTable
DoctorID Label
1 MRI
1 MRIWO
1 MRI
1 MRI
2 MRIWWO
2 MRIWO
1 MRIWWO
1 MRIWO
[/tt]

and you want to return for Doctor 1:
[tt]
Label Count
MRI 3
MRIWO 2
MRIWWO 1[/tt]

is that not what your table has? Is that not what results you want?

Perhaps YOU could provide some sample data and expected results and then we would have a clearer idea of what you are trying to do.....

Leslie

 

[r937]

ooh, ooh, i know this one, pick me, pick me

select doctorid
  from sometable
 where Label in ( 'MRI','MRIWO','MRIWWO' )
group
    by doctorid
having count(distinct label) = 3

r937.com | rudy.ca

 

[PHV]

Rudy, JetSQL lacks the COUNT(DISTINCT colname) aggregate function ...

 

[r937]

aw, rats, i knew that, i just forgot which forum i was in ;-)

the only reason i wrote that instead of the more usual COUNT(*) is because i anticipated multiple occurrences

we need more info from the original poster regarding the actual table definition and the occurrences of these values

r937.com | rudy.ca

 

[ProtocolPirate]

Actually, as it turns out I've got 98 different categories, most of which I want to group into only 5 or 6 different groups.

So for example, how would I sum the total CT referrals vs. the number of MRI referrals for all doctors? I would have to run a separate query to get each column the way you are describing it, wouldn't I?

 

[lespaul]

depends on how your table is set up....if you'll share that....

 

[ProtocolPirate]

Rudy is right about my wanting to group by the DoctorName field, not by StudyType. So for each doctor I want a SUM() WHERE Charge.GrpName LIKE 'MRI%', but on the same line in another column I also want the SUM() WHERE Charge.GrpName LIKE 'CT%'

 

[ProtocolPirate]

The table structure is a mess, I have to get the referring doctor from one of a dozen different posting tables that I have to union all together. For the sake of brevity I'll just call it the Posting table.

select doc.name, SUM(study.GrpName Like 'MRI%'), SUM(study.GrpName Like 'CT%')
FROM Daysheet day INNER JOIN Posting pst ON pst.ChargeNumber=day.ChargeNumner
INNER JOIN StudyTypes study ON study.Id=day.StudyID
INNER JOIN Doctors doc ON doc.Id=pst.RefPhyCode
GROUP BY doc.name

 

[r937]

select doc.name
, SUM(case when study.GrpName Like 'MRI%'
then 1 else 0 end) as MRI
, SUM(case when study.GrpName Like 'CT%'
then 1 else 0 end) as CT
FROM ...

r937.com | rudy.ca

 

[lespaul]

nope rudy, it's Access, no case statement, iif statements:

SELECT Doc.Name, SUM(iif(study.GrpName Like 'MRI%', 1, 0) As MRI, SUM(iif(study.GrpName Like 'CT%', 1, 0) As CT
FROM ...

great translation of needs to requirements though!

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[r937]

curses, forgot which forum i was in again!

;-)

r937.com | rudy.ca

 

[ProtocolPirate]

Ah ha! I was wondering how this could do very much without conditionals.

Thank you very much!

 

[PHV]

And in JetSQL the wildcard is * (not %)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

nice catch PH, totally missed that during my copy and paste...