Trying to find distinct number of items in several co-related columns

user_17605704 0
Viewed 34

I have a table with 3 columns namely- Business function, Hosts and It Services. A business function owns multiple hosts and each host has multiple services associated with it.

An example of the table is as follows -

Business Function Host Name It Services
Commercial Banking GigaTux AAA
Commercial Banking GigaTux CCC
Wealth HTX RRR
Wealth HTX DDD
Commercial Banking KDP AAA
Wealth Fusion FFF
Commercial Banking CreateX QQQ
Wealth Icon ZZZ

I need to find the number of distinct hosts within a business function and the number of shared hosts which have more than 1 IT services mapped to it within the distinct hosts of that business function.

The desired table is as follows (The name of the table is es_dashboard) -

Business Function Host Shared Hosts
Commercial Banking 3 1
Wealth 3 1

This is because Commercial banking has 3 distinct hosts- GigaTux, KDP and CreateX, and there's only 1 host (GigaTux) which has more than 1 IT Service mapped to it. Same thing applies to Wealth as well.

My current SQL code is as follows-

SELECT ES.business_function AS 'Business Function' , COUNT(DISTINCT host) AS 'Host ', 
    (SELECT count(*)
        FROM es_dashboard ESO     
        WHERE ES.business_function = ESO.business_function 
        AND ESO.host IN
            (SELECT EST.host
            FROM es_dashboard EST
            WHERE ES.business_function = EST.business_function AND EST.host = ESO.host AND count(distinct EST.it_service) > 2)
    ) AS "Shared Hosts"
FROM es_dashboard ES
GROUP BY BF;

The goal is to use a nested query without creating any new tables.

I can get the distinct hosts within a business function but having trouble in finding out the distinct IT services. Can someone help?

nested-query sql
1 Answers

You can use multiple sub-queries to achieve this:

Query:

SELECT 
    hst.Business_Function, 
    hst.HOST, 
    COALESCE (shr.SHARED_HOST,0) AS SHARED_HOST
FROM 
   (
    SELECT 
       Business_Function,
       COUNT(distinct Host_Name) as HOST
    FROM es_dashboard
    GROUP BY Business_Function
    ) AS hst

LEFT JOIN
   (
    SELECT Business_Function, 
          COUNT(DISTINCT HOST_Name) AS SHARED_HOST
    FROM
        (
         SELECT Business_Function, Host_Name, 
                COUNT(distinct It_services) AS it_cnt
         FROM es_dashboard AS esd
         GROUP BY Business_Function, Host_Name
        ) AS it
    WHERE it_cnt>1
    GROUP BY Business_Function
) AS shr
ON hst.Business_function = shr.business_function

Query explanation:

  1. The sub-query in FROM clause will give us the count of HOSTS in each business function
  2. The one in JOIN clause will give us the count of HOSTS with more than 1 it_services
  3. Then we LEFT JOIN because there might be a chance that NONE of the hosts in a business function have >1 it_services
  4. Using COALESCE to handle such cases (modify as per your requirement)

See DEMO here

user_170156930
Related