Return count of total group membership when providers are part of a group

Question

TABLE A: Pre-joined table - Holds a list of providers who belong to a group and the group the provider belongs to. Columns are something like this:

ProviderID (PK, FK) | ProviderName | GroupID | GroupName 

 1234               | LocalDoctor  | 987     | LocalDoctorsUnited
 5678               | Physican82   | 987     | LocalDoctorsUnited
 9012               | Dentist13    | 153     | DentistryToday
 0506               | EyeSpecial   | 759     | OphtaSpecialist

TABLE B: Another pre-joined table, holds a list of providers and their demographic information. Columns as such:

ProviderID (PK,FK)  | ProviderName  | G_or_I | OtherColumnsThatArentInUse

 1234               | LocalDoctor  | G       | Etc.
 5678               | Physican82   | G       | Etc.
 9012               | Dentist13    | I       | Etc.
 0506               | EyeSpecial   | I       | Etc.

The expected result is something like this:

 ProviderID         | ProviderName | ProviderStatus | GroupCount

 1234               | LocalDoctor  | Group          | 2
 5678               | Physican82   | Group          | 2
 9012               | Dentist13    | Individual     | N/A
 0506               | EyeSpecial   | Individual     | N/A

The goal is to determine whether or not a provider belongs to a group or operates individually, by the G_or_I column. If the provider belongs to a group, I need to include an additional column that provides the count of total providers in that group.

The Group/Individual portion is relatively easy - I've done something like this:

SELECT DISTINCT
 A.ProviderID,
 A.ProviderName,
 CASE
  WHEN B.G_or_I = 'G'
   THEN 'Group'
  WHEN B.G_or_I = 'I'
   THEN 'Individual' END AS ProviderStatus
FROM
 TableA A
  LEFT OUTER JOIN TableB B
   ON A.ProviderID = B.ProviderID;

So far so good, this returns the expected results based on the G_or_I flag.

However, I can't seem to wrap my head around how to complete the COUNT portion. I feel like I may be overthinking it, and stuck in a loop of errors. Some things I've tried:

Add a second CASE STATEMENT:

CASE
 WHEN B.G_or_I = 'G'
  THEN (
        SELECT CountedGroups 
        FROM (
               SELECT ProviderID, count(GroupID) AS CountedGroups 
               FROM TableA
               WHERE A.ProviderID = B.ProviderID
               GROUP BY ProviderID --originally had this as ORDER BY, but that was a mis-type on my part
             )
       )
  ELSE 'N/A' END

This returns an error stating that a single row sub-query is returning more than one row. If I limit the number of rows returned to 1, the CountedGroups column returns 1 for every row. This makes me think that its not performing the count function as I expect it to.

I've also tried including a direct count of TableA as a factored sub-query:

WITH CountedGroups AS
( SELECT Provider ID, count(GroupID) As GroupSum
  FROM TableA
  GROUP BY ProviderID --originally had this as ORDER BY, but that was a mis-type on my part
) --This as a standalone query works just fine

  SELECT DISTINCT
     A.ProviderID,
     A.ProviderName,
     CASE
      WHEN B.G_or_I = 'G'
       THEN 'Group'
      WHEN B.G_or_I = 'I'
       THEN 'Individual' END AS ProviderStatus,
    CASE
      WHEN B.G_or_I = 'G'
       THEN GroupSum
      ELSE 'N/A' END
    FROM
     CountedGroups CG
      JOIN TableA A
       ON CG.ProviderID = A.ProviderID
      LEFT OUTER JOIN TableB
       ON A.ProviderID = B.ProviderID

This returns either null or completely incorrect column values

Other attempts have been a number of variations of this, with a mix of bad results or Oracle errors. As I mentioned above, I'm probably way overthinking it and the solution could be rather simple. Apologies if the information is confusing or I've not provided enough detail. The real tables have a lot of private medical information, and I tried to translate the essence of the issue as best I could.

Thank you.

user_11565629 · Answer

You can use the CASE..WHEN and analytical function COUNT as follows:

SELECT
    A.PROVIDERID,
    A.PROVIDERNAME,
    CASE
        WHEN B.G_OR_I = 'G' THEN 'Group'
        ELSE 'Individual'
    END AS PROVIDERSTATUS,
    CASE
        WHEN B.G_OR_I = 'G' THEN TO_CHAR(COUNT(1) OVER(
            PARTITION BY A.GROUPID
        ))
        ELSE 'N/A'
    END AS GROUPCOUNT
FROM
    TABLE_A A
    JOIN TABLE_B B ON A.PROVIDERID = B.PROVIDERID;

TO_CHAR is needed on COUNT as output expression must be of the same data type in CASE..WHEN

user_2299760 · Answer

Your problem seems to be that you are missing a column. You need to add group name, otherwise you won't be able to differentiate rows for the same practitioner who works under multiple business entities (groups). This is probably why you have a DISTINCT on your query. Things looked like duplicates which weren't. Once you've done that, just use an analytic function to figure out the rest:

SELECT ta.providerid, 
       ta.providername, 
       DECODE(tb.g_or_i, 'G', 'Group', 'I', 'Individual') AS ProviderStatus,
       ta.group_name,
       CASE
         WHEN tb.g_or_i = 'G' THEN COUNT(DISTINCT ta.provider_id) OVER (PARTITION BY ta.group_id)
         ELSE 'N/A'
       END AS GROUP_COUNT
FROM table_a ta
INNER JOIN table_b tb ON ta.providerid = tb.providerid

Is it possible that your LEFT JOIN was going the wrong direction? It makes more sense that your base demographic table would have all practitioners in it and then the Group table might be missing some records. For instance if the solo prac was operating under their own SSN and Type I NPI without applying for a separate Type II NPI or TIN.

Return count of total group membership when providers are part of a group

2 Answers