How to create sums/counts of grouped items over multiple tables

DISCLAIMER: The title might be misleading.

Introduction

I have aquired the wonderful project of bringing all our SQL Server instances up-to-date, which means I have to ensure that the approrpriaate Service Packs are applied to each instance.

We have a database tools (abbreviated as DBT) application that stores all the information of our MySQL, PostgreSQL, Microsoft SQL Server and Oracle RDBMS databases in one location.

This DBT-application links a given database to an application, the database to an instance and the instance to a server, and of course the database to a responsible person.

A database will and can have a lot of additional information (DB Version, Status, Project Manager, Database Manager, ...) which I have kept out of the description to simplify the explanation.

To get the project going I wanted to output a list of unique SQL Servers with a sum of the databases and grouped by all other relevant information.
The idea was to have an overview of the SQL Servers having the most databases and the highest complexity (users, applications, instances).

TL;DR

Here is a sample of already summarized data and what I was expecting to achieve

Sample result set

```
SRV_NAME INST_NAME DB_NAME USER_NAME APPL_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
SQLSRV_01 ANOTHER HIS_DB HIM TELLTAIL
SQLSRV_01 ANOTHER RZO_P4 YOU PSB IZQ
SQLSRV_01 GENERAL MY_DB2 ME HAL_2000
SQLSRV_01 GENERAL MY_DB3 ME HAL_2000
SQLSRV_01 GENERAL MY_DB4 ME HAL_2000
SQLSRV_01 GENERAL RZO_6_4 ME

It appears you want COUNT(DISTINCT), which gives you the count of unique values in a column – seems to be exactly what you want.

SELECT s.[SRV_NAME],
       GRP_CNT_INST_NAME = COUNT(DISTINCT i.[INST_NAME]),
       SUM_DB_NAME       = COUNT(*),
       GRP_CNT_USER_NAME = COUNT(DISTINCT u.[USER_NAME]),
       GRP_CNT_APPL_NAME = COUNT(DISTINCT a.[APPL_NAME])
FROM   [DBT].[Server]            AS s
       JOIN [DBT].[Instance]     AS i
            ON  s.ID = i.SRV_ID
       JOIN [DBT].[Database]     AS d
            ON  i.[ID] = d.[INST_ID]
       JOIN [DBT].[Application]  AS a
            ON  d.[APPL_ID] = a.[ID]
       JOIN [DBT].[User]         AS u
            ON  u.ID = d.[USER_ID]
GROUP BY s.[SRV_NAME];

Based on your joins, it appears that DB rows are going to be unique, so you probably do not need COUNT(DISTINCT) in that specific instance. If the output should reflect the number of actual databases, counting distinct names can give you a skewed result, since different instances might have databases with identical names and COUNT(DISTINCT) would see that as a single item.

On the other hand, there would probably be no issue if you counted IDs rather than names:

SELECT s.[SRV_NAME],
       GRP_CNT_INST_NAME = COUNT(DISTINCT i.[ID]),
       SUM_DB_NAME       = COUNT(DISTINCT d.[ID]),
       GRP_CNT_USER_NAME = COUNT(DISTINCT u.[ID]),
       GRP_CNT_APPL_NAME = COUNT(DISTINCT a.[ID])
FROM   [DBT].[Server]            AS s
       JOIN [DBT].[Instance]     AS i
            ON  s.ID = i.SRV_ID
       JOIN [DBT].[Database]     AS d
            ON  i.[ID] = d.[INST_ID]
       JOIN [DBT].[Application]  AS a
            ON  d.[APPL_ID] = a.[ID]
       JOIN [DBT].[User]         AS u
            ON  u.ID = d.[USER_ID]
GROUP BY s.[SRV_NAME];

StackExchange Database Administrators Q#163577, answer score: 4