Multiple tables condensed to one row with various totals

Question

We have Applications, which run on Hosts, which have Software on them.

An Application can run on many Hosts. A Host typically has many Software items loaded onto it.

We capture this information with an Application table, and two more tables to capture the relationships, Application-Host, Host-Software.

It is possible, however, that a Host does not have Software. (It might be genuinely empty or we might not have information yet, both need to be highlighted.)

For each Application, I need to count the Hosts having software on them. I cannot find a way to do it.

Assume an Application has 5 Hosts, four of which are connected to Software. One relationship table contains 5 Hosts, the other many instances of 4 Hosts, but the 4 are indirectly connected through the first relationship table.

How can I get the correct answer, 4? Whatever I do I get 5, or the total number of Software items on all hosts.

---

This is what I have so far, including debug code.

select distinct
AH.APPLICATION_X_COMPONENT_NAME,
count(case when T.TECH_ITEM_REL_ITCM_CAT = 'Operating System' then 1 end) over      (partition by AH.APPLICATION_X_COMPONENT_NAME) as NoOfOpsys,
x.APPLICATION_X_COMPONENT_NAME,
x.HOST_COMPONENT_NAME,
x.NoHostsWithTIR
from dbo.CTO_TechnologyItemRelease as T

inner join dbo.CTOR_HOST_TECHITEMRELEASE as HT
on HT.TECH_ITEM_RELEASE_COMP_ID = T.TechnologyItemReleaseComponent

inner join dbo.CTOR_APPLICATIONX_HOST as AH
on AH.HOST_COMPONENT_ID = HT.HOST_COMPONENT_ID

inner join
(
select distinct
AH2.APPLICATION_X_COMPONENT_NAME,
AH2.HOST_COMPONENT_NAME,
count(case when HT2.HOST_COMPONENT_NAME is not null then 1 end) over (partition by AH2.APPLICATION_X_COMPONENT_NAME, HT2.HOST_COMPONENT_NAME) as NoHostsWithTIR
from dbo.CTOR_APPLICATIONX_HOST as AH2

inner join dbo.CTOR_HOST_TECHITEMRELEASE as HT2
on HT2.HOST_COMPONENT_NAME = AH2.HOST_COMPONENT_NAME
)
as x on x.APPLICATION_X_COMPONENT_NAME = AH.APPLICATION_X_COMPONENT_NAME

order by AH.APPLICATION_X_COMPONENT_NAME

Data:

App table
App_ID     App_Name
A0001      Application_1
A0002      Application_2
A0003      Application_3
A0004      Application_4

App-Host table
App_ID     App_Name              Host_ID  Host_Name
A0001      Application_1         H0001    Host_1
A0002      Application_2         H0001    Host_1
A0002      Application_2         H0002    Host_2
A0002      Application_2         H0003    Host_3
A0002      Application_2         H0004    Host_4
A0003      Application_3         H0005    Host_5
A0004      Application_4         H0002    Host_2
A0004      Application_4         H0006    Host_6

Host-TI table
Host_ID    Host_Name   TI_ID    TI_Name
H0001      Host_1      T0001    MS SQL Server 2005 SP1
H0001      Host_1      T0002    MS Windows Server 2008
H0002      Host_2      T0002    Red Hat Enterprise Linux 3
H0003      Host_3      T0002    MS Windows Server 2008
H0003      Host_3      T0003    Oracle Database Server 9i 9.2    
H0005     Host_5      T0001    MS SQL Server 2005 SP1
H0006      Host_6      T0004    Tivoli Storage Manager 5.2

TI table
TI_ID    TI_Name                          TI_Type
T0001    MS SQL Server 2005 SP1           Software Product
T0002    MS Windows Server 2008           Operating System
T0003    Red Hat Enterprise Linux 3       Operating System
T0003    Oracle Database Server 9i 9.2    Software Product
T0004    Tivoli Storage Manager 5.2       Software Product

The required output

App Name          Operating System count        Hosts with Tech Items
Application_1     1                             1       
Application_2     3                             3
Application_3     0                             1
Application_4     1                             2

The crucial line is Application_2 having 3 hosts with tech items. I can only get a 4 in this position and my Operating System count goes on the blink frequently.

Answer 1

I agree with @SWeko on the point of inconsistency in your data samples and here's also my take on the problem:

SELECT
  ap.App_Name,
  OperatingSystemCount = COUNT(ti.TI_ID),
  HostsWithTechItems   = COUNT(DISTINCT ah.Host_ID)
FROM
  App AS ap
  LEFT JOIN
    AppHost AS ah
    INNER JOIN HostTI AS ht ON ah.Host_ID = ht.Host_ID
    LEFT JOIN TI AS ti ON ht.TI_ID = ti.TI_ID AND ti.TI_Type = 'Operating System'
  ON ap.App_ID = ah.App_ID
GROUP BY
  ap.App_Name
;

A SQL Fiddle demo for this query can be found here: http://sqlfiddle.com/#!3/677ae/3

Note that when counting operating systems, the query doesn't recognise the fact that some of them may be installed on the same host: in that case, each instance would be counted. If you actually meant to count hosts with operating systems instead, you would need to change the above query by replacing COUNT(ti.TI_ID) with something like

COUNT(DISTINCT CASE WHEN ti.TI_ID IS NOT NULL THEN ah.Host_ID END)

Answer 2

The database design seems seriously fubar'd, so the data appear to be inconsistent:

These rows appear to contradict one another:

H0001      Host_1      T0002    MS Windows Server 2008
H0002      Host_2      T0002    Red Hat Enterprise Linux 3

as do these:

T0003    Red Hat Enterprise Linux 3       Operating System
T0003    Oracle Database Server 9i 9.2    Software Product

I'm gonna assume that the data is invalid, and that the correct data is along the lines in the SQL Fiddle.

Under that assumption this code will get you what you need:

select a.app_ID, a.app_name, 
  (select Count(*) 
     from apphost ah
       inner join hostti ht on ah.host_id = ht.host_id
       inner join ti on ht.ti_id=ti.ti_id
   where ti_type = 'Operating System'
     and ah.app_id = a.app_id) as OSCount,
  (select Count(distinct ah.host_id) 
     from apphost ah
       inner join hostti ht on ah.host_id = ht.host_id
   where ah.app_id = a.app_id) as HostWithSoftwareCount
from App a 

Answer 3

Something like this:

select a.name, COUNT(*)
from application a
join application_host ah on (a.id = ah.application)
join host h on (h.id = ah.host)
where exists (select top 1 1 from host_software hs where hs.host = ah.host)
group by a.name

http://sqlfiddle.com/#!2/c4bbb/2