Oracle SQL - Counting distinct column combinations

Question

Running Oracle 12.1. I have a Line Items table. Its structure is fixed, and I cannot change it. I need to build a dashboard style page of information of the Line items table for a person to look at their sales territory. This person might be a GVP, who owns a large territory, or a Manager, or an individual rep. The Line Items table is pretty de-normalized, as this copy is part of a DW. This ‘copy’ of the table is only updated every 2 weeks, and it looks like this.

Line_Item_ID // PK
Account_ID // 
Company_Name // The legal name of the Headquarters
LOB_Name  // Line of business, aka Division within the Company_Name
Account_Type // One of 2 values, ‘NAMED’ or “GENERAL’
ADG_STATUS // 3 possible values, ‘A’, ‘D’ or ‘G’
Industry  // One of 15 values, for this example assume it is ONLY ‘MFG’, ‘GOV’, ‘HEALTHCARE’
 // Now have the sales hierarchy of the rep who sold this
GVP // Group Vice President
SVP // Sales Vice President
RVP // Regional Vice President
RM // Regional Manager
REP // Sales Rep
 // Now have information about the product sold
ProductName
ProductPrice
VariousOtherFields….

I need to make an aggregated table which will be used for quick access of the dashboard. It will have counts of various combinations, and there will be one row per PERSON, not account. A person is every UNIQUE person listed in any of the GVP, SVP, RVP, RM or REP fields. Here is what the end result table will look like. Other than PERSON, every column is based on a DISTINCT count, and it is an integer value.

PERSON
TOTAL_COMPANIES // For this person, count of DISTINCT COMPANY_NAME
TOTAL_LOBS // For this person, count of DISTINCT LOBS
TOTAL_COMPANIES_NAMED // count of DISTINCT COMPANY_NAME with ACCOUNT_TYPE=’NAMED’
TOTAL_COMPANIES_GENERAL // count of DISTINCT COMPANY_NAME with ACCOUNT_TYPE=’GENERAL’
TOTAL_LOBS_NAMED  // count of DISTINCT LOB_NAME with ACCOUNT_TYPE=’NAMED’
TOTAL_LOBS_GENERAL // count of DISTINCT LOB_NAME with ACCOUNT_TYPE=’GENERAL’
TOTAL_COMPANIES_STATUS_A // count of DISTINCT COMPANY_NAME with ADG_STATUS=’A’
TOTAL_COMPANIES_STATUS_D // count of DISTINCT COMPANY_NAME with ADG_STATUS=’D’
TOTAL_COMPANIES_STATUS_G // count of DISTINCT COMPANY_NAME with ADG_STATUS=’G’
TOTAL_LOB_STATUS_A  // count of DISTINCT  LOB_NAME with ADG_STATUS=’A’
TOTAL_LOB_STATUS_D // count of DISTINCT  LOB_NAME with ADG_STATUS=’D’
TOTAL_LOB_STATUS_G // count of DISTINCT  LOB_NAME with ADG_STATUS=’G’
//Now Various Industry Permutations.  I have 15 different industries, but only showing 2.  This will only be at the COMPANY_NAME level, not the LOB_NAME level
MFG_COMPANIES_STATUS_A // count of DISTINCT COMPANY_NAME with ADG_STATUS=’A’ and Industry = ‘MFG’
MFG_COMPANIES_STATUS_D // count of DISTINCT COMPANY_NAME with ADG_STATUS=’D’ and Industry = ‘MFG’
MFG_COMPANIES_STATUS_G // count of DISTINCT COMPANY_NAME with ADG_STATUS=’G’ and Industry = ‘MFG’

GOV_COMPANIES_STATUS_A // count of DISTINCT COMPANY_NAME with ADG_STATUS=’A’ and Industry = ‘GOV’
GOV_COMPANIES_STATUS_D // count of DISTINCT COMPANY_NAME with ADG_STATUS=’D’ and Industry = ‘GOV’
GOV_COMPANIES_STATUS_G // count of DISTINCT COMPANY_NAME with ADG_STATUS=’G’ and Industry = ‘GOV’

There are approx. 400 people, 35000 unique accounts, and 200,000 entries in the line items table.

So what is my strategy? I have thought about making another table of unique PERSON values, and using it as a driving table. Let’s call this table PERSON_LIST.

Pseudo-code…

For each entry in PERSON_LIST
   For all LINE_ITEMS where person_list in ANY(GVP, SVP, RVP, RM, REP) do
      Calculations…

This would be an incredibly long running process…

How can I do this more effectively (set based as opposed to row by row)? I believe I would have to use the PIVOT operator for the INDUSTRY list, but can I use PIVOT with additional criteria? Aka count of distinct COMPANY with a specific industry and a specific ADG_STATUS?

Any ideas or SQL code most appreciated.

Answer 1

You could unpivot the original data to get the data from the original GVP etc. columns into one 'person' column:

select * from line_items
unpivot (person for  role in (gvp as 'GVP', svp as 'SVP', rvp as 'RVP',
  rm as 'RM', rep as 'REP'))

And then use that as a CTE or inline view, with pretty much what you showed; conditional aggregation using case expressions, something like:

select person,
  count(distinct company_name) as total_companies,
  count(distinct lob_name) as total_lobs,
  count(distinct case when account_type='NAMED' then company_name end)
    as total_companies_named,
  count(distinct case when account_type='GENERAL' then company_name end)
    as total_companies_general,
  count(distinct case when account_type='NAMED' then lob_name end)
    as total_lobs_named,
  count(distinct case when account_type='GENERAL' then lob_name end)
    as total_lobs_general,
  count(distinct case when adg_status='A' then company_name end)
    as total_companies_status_a,
  count(distinct case when adg_status='D' then company_name end)
    as total_companies_status_d,
  count(distinct case when adg_status='G' then company_name end)
    as total_companies_status_g,
  count(distinct case when adg_status='A' then lob_name end)
    as total_lob_status_a,
  count(distinct case when adg_status='D' then lob_name end)
    as total_lob_status_d,
  count(distinct case when adg_status='G' then lob_name end)
    as total_lob_status_g,
  count(distinct case when adg_status='A' and industry = 'MFG' then company_name end)
    as mfg_companies_status_a,
  count(distinct case when adg_status='D' and industry = 'MFG' then company_name end)
    as mfg_companies_status_d,
  count(distinct case when adg_status='G' and industry = 'MFG' then company_name end)
    as mfg_companies_status_g,
  count(distinct case when adg_status='A' and industry = 'GOV' then company_name end)
    as gov_companies_status_a,
  count(distinct case when adg_status='D' and industry = 'GOV' then company_name end)
    as gov_companies_status_d,
 count(distinct case when adg_status='G' and industry = 'GOV' then company_name end)
    as gov_companies_status_g
from (
  select * from line_items
  unpivot (person for  role in (gvp as 'GVP', svp as 'SVP', rvp as 'RVP',
    rm as 'RM', rep as 'REP'))
)
group by person;