Display all possible matches with count in SQL-CodePudding

I have a table as shown below

Id	FName	Gender	StatusValue
1	Sam	Male	SV001
2	Emma	Female	SV002
3	Ava	Unknown
4	John	Male
5	Olivia	Female
6	Joe	Male
7	Mia	Female

I want to display the below output

Id	Gender	StatusValue	Count
1	Female	SV001	0
2	Female	SV002	1
3	Female	Empty	2
4	Male	SV001	1
5	Male	SV002	0
6	Male	Empty	2
7	Unknown	SV001	0
8	Unknown	SV002	0
9	Unknown	Empty	1

Here I want to display all possible combinations with matching counts. Note: cannot use common table expressions and PIVOT, since the tool is not supporting few of the SQL keywords. I have tried the below but it is giving wrong count

SELECT Gender,StatusValue,Count(*) as Count FROM Persons 
WHERE Gender <> '' AND StatusValue <> '' 
GROUP BY Gender,StatusValue 
UNION
SELECT 
CASE WHEN G.Gender <> '' THEN G.Gender ELSE 'Empty' END,
CASE WHEN T.StatusValue <> '' THEN T.StatusValue ELSE 'Empty' END, 0 as COUNT from
(SELECT DISTINCT Gender FROM Persons) as G, (SELECT DISTINCT StatusValue FROM Persons) as T

CodePudding user response：

You can do:

select x.gender, y.status_value, count(id)
from (select distinct gender from t) x
cross join (select distinct status_value from t) y
left join t on (t.gender = x.gender or coalesce(t.gender, x.gender) is null)
  and (t.status_value = y.status_value or coalesce(t.status_value, y.status_value) is null)
group by x.gender, y.status_value
order by x.gender, y.status_value

Result:

 gender   status_value  (No column name) 
 -------- ------------- ---------------- 
 Female   null          2                
 Female   SV001         0                
 Female   SV002         1                
 Male     null          2                
 Male     SV001         1                
 Male     SV002         0                
 Unknown  null          1                
 Unknown  SV001         0                
 Unknown  SV002         0

The left join predicate is unnecessarily long due to the lack of the IS NOT DISTINCT FROM operator in SQL Server. Apparently it's being implemented in SQL Server 2022, though.

See running example at db<>fiddle.

Note: The restriction not to use CTEs or pivot is somewhat artificial since SQL Server does support them.

CodePudding user response：

You can do one subquery to get all distinct combinations, and another to do your counting. In the end just LEFT JOIN two for desired results:

SELECT Cmb.Gender, Cmb.StatusValue, COALESCE(Cnt.Count, 0) AS Count
FROM
(
  SELECT 
    CASE WHEN G.Gender <> '' THEN G.Gender ELSE 'Empty' END AS Gender
  , CASE WHEN T.StatusValue <> '' THEN T.StatusValue ELSE 'Empty' END AS StatusValue
  FROM
  (SELECT DISTINCT Gender FROM Persons) as G 
  CROSS JOIN (SELECT DISTINCT StatusValue FROM Persons) as T
) AS Cmb  -- cominations
LEFT JOIN 
(
  SELECT 
   CASE WHEN Gender <> '' THEN Gender ELSE 'Empty' END AS Gender
  ,CASE WHEN StatusValue <> '' THEN StatusValue ELSE 'Empty' END AS StatusValue, Count(*) as Count 
  FROM Persons 
  GROUP BY CASE WHEN Gender <> '' THEN Gender ELSE 'Empty' END, CASE WHEN StatusValue <> '' THEN StatusValue ELSE 'Empty' END 
) AS Cnt -- counts
ON Cmb.Gender = Cnt.Gender AND Cmb.StatusValue = Cnt.StatusValue

CodePudding user response：

Working Fiddle Here,

Assuming,

CREATE TABLE [X]
(
    [Id] INT NOT NULL PRIMARY KEY,
    [FName] VARCHAR(10) NOT NULL,
    [Gender] VARCHAR(10) NOT NULL,
    [StatusValue] VARCHAR(10) NULL
);

This works,

;WITH S AS
(
  SELECT 'ZZZZZZZZ' [Sub]
),
G AS
(
  SELECT DISTINCT [Gender] FROM [X]
),
SV AS
(
  SELECT DISTINCT [StatusValue] FROM [X]
),
C AS
(
  SELECT
      G.[Gender],
      ISNULL(SV.[StatusValue], S.[Sub]) [StatusValue]
  FROM
      G
    CROSS JOIN
      SV
    CROSS JOIN
      S
),
D AS
(
  SELECT
    X.[Gender],
    ISNULL(X.[StatusValue], S.[Sub]) [StatusValue]
  FROM
    X
   CROSS JOIN
    S
),
A AS
(
  SELECT
      COUNT(*) [Count],
      D.[Gender],
      D.[StatusValue]
    FROM
      D
    GROUP BY
      D.[Gender],
      D.[StatusValue]
)
SELECT
        ROW_NUMBER() OVER(ORDER BY C.[Gender], C.[StatusValue]) [Id],
        C.[Gender],
        CASE
            WHEN C.[StatusValue] = S.[Sub]
               THEN 'Empty'
            ELSE
               C.[StatusValue]
        END [StatusValue],
        ISNULL(A.[Count], 0) [Count]
   FROM
        C
     LEFT JOIN
        A
          ON A.[Gender] = C.[Gender] AND A.[StatusValue] = C.[StatusValue]
     CROSS JOIN
        S;

Or, Without CTEs,

SELECT
        ROW_NUMBER() OVER(ORDER BY C.[Gender], C.[StatusValue]) [Id],
        C.[Gender],
        CASE
            WHEN C.[StatusValue] = 'ZZZZZZZZZZ'
               THEN 'Empty'
            ELSE
               C.[StatusValue]
        END [StatusValue],
        COALESCE(A.[Count], 0) [Count]
   FROM
        (
        SELECT
                G.[Gender],
                COALESCE(SV.[StatusValue], 'ZZZZZZZZZZ') [StatusValue]
            FROM
                (SELECT DISTINCT [Gender] FROM [X]) G
              CROSS JOIN
                (SELECT DISTINCT [StatusValue] FROM [X]) SV
        ) C
     LEFT JOIN
        (
            SELECT
                COUNT(*) [Count],
                D.[Gender],
                D.[StatusValue]
              FROM
                (
                SELECT
                    X.[Gender],
                    COALESCE(X.[StatusValue], 'ZZZZZZZZZZ') [StatusValue]
                 FROM
                    X
                ) D
              GROUP BY
                D.[Gender],
                D.[StatusValue]
        ) A
          ON A.[Gender] = C.[Gender] AND A.[StatusValue] = C.[StatusValue];

Both produce

Id	Gender	StatusValue	Count
1	Female	SV001	0
2	Female	SV002	1
3	Female	Empty	2
4	Male	SV001	1
5	Male	SV002	0
6	Male	Empty	2
7	Unknown	SV001	0
8	Unknown	SV002	0
9	Unknown	Empty	1