SQL Server Include GROUP BY in query 1 of a UNION, instead of two queries UNIONed to get fill rates/row counts by category id

4si2a6ki 于 2023-03-28 发布在 Go

关注(0)|答案(1)|浏览(234)

I have a table of example data:

CREATE TABLE #TempTable 
(
    CategoryID varchar(10), --Non-Unique
    ArbitraryColumnName varchar(10), --Non-Unique
)

INSERT INTO #TempTable (CategoryID, ArbitraryColumnName)
VALUES
    ('1', '14578123'),
    ('2', NULL),
    ('1', '13402348'),
    ('4', '12302354'),
    ('4', NULL),
    ('5', '12678912')

CategoryID	ArbitraryColumnName
1	14578123
2	NULL
1	13402348
4	12302354
4	NULL
5	12678912

I am trying to get the counts for all of the categories together, and the separates categories, in the same table. Here is my query to do so:

SELECT
    CAST('TempTable' AS varchar(55)) AS TableName
    ,CAST('ArbitraryColumnName' AS varchar(55)) AS FieldName
    ,CAST('All CategoryIDs' AS varchar(55)) CategoryID
    ,COUNT(*) AS Table_RowCount
    ,SUM(CASE WHEN a.ArbitraryColumnName IS NOT NULL THEN 1 ELSE 0 END) AS Field_NonNullCount
    ,COUNT(DISTINCT a.ArbitraryColumnName) AS Field_DistinctCount
    ,CAST(100 * AVG(CASE WHEN a.ArbitraryColumnName IS NOT NULL THEN 1.0 ELSE 0.0 END) AS 
numeric(10,2)) AS Field_FillRate
FROM #TempTable a

UNION

SELECT
    CAST('TempTable' AS varchar(55)) AS TableName
    ,CAST('ArbitraryColumnName' AS varchar(55)) AS FieldName
    ,CAST(a.CategoryID AS varchar(55)) GroupID
    ,COUNT(*) AS Table_RowCount
    ,SUM(CASE WHEN a.ArbitraryColumnName IS NOT NULL THEN 1 ELSE 0 END) AS Field_NonNullCount
    ,COUNT(DISTINCT a.ArbitraryColumnName) AS Field_DistinctCount
    ,CAST(100 * AVG(CASE WHEN a.ArbitraryColumnName IS NOT NULL THEN 1.0 ELSE 0.0 END) AS 
numeric(10,2)) AS Field_FillRate
FROM #TempTable a
GROUP BY a.CategoryID

Results:

TableName	FieldName	CategoryID	Table_RowCount	Field_NonNullCount	Field_DistinctCount	Field_FillRate
TempTable	ArbitraryColumnName	1	2	2	2	100.00
TempTable	ArbitraryColumnName	2	1	0	0	0.00
TempTable	ArbitraryColumnName	4	2	1	1	50.00
TempTable	ArbitraryColumnName	5	1	1	1	100.00
TempTable	ArbitraryColumnName	All CategoryIDs	6	4	4	66.67

What is the best way to combine the queries of the UNION to provide the same results?

sql-server

来源：https://stackoverflow.com/questions/75858645/include-group-by-in-query-1-of-a-union-instead-of-two-queries-unioned-to-get-fi

1条答案

按热度按时间

uoifb46i1#

I can write the above Union query as in this DBFIDDLE_DEMO

Which gives the same output as above :

SELECT
    CAST('TempTable' AS varchar(55)) AS TableName,
    CAST('ArbitraryColumnName' AS varchar(55)) AS FieldName,
    COALESCE(CAST(a.CategoryID AS varchar(55)), 'All CategoryIDs') AS CategoryID,
    COUNT(*) AS Table_RowCount,
    SUM(CASE WHEN a.ArbitraryColumnName IS NOT NULL THEN 1 ELSE 0 END) AS Field_NonNullCount,
    COUNT(DISTINCT a.ArbitraryColumnName) AS Field_DistinctCount,
    CAST(100 * AVG(CASE WHEN a.ArbitraryColumnName IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS Field_FillRate
FROM #TempTable a
GROUP BY GROUPING SETS((a.CategoryID), ());

Output :

TableName	FieldName	CategoryID	Table_RowCount	Field_NonNullCount	Field_DistinctCount	Field_FillRate
TempTable	ArbitraryColumnName	1	2	2	2	100.00
TempTable	ArbitraryColumnName	2	1	0	0	0.00
TempTable	ArbitraryColumnName	4	2	1	1	50.00
TempTable	ArbitraryColumnName	5	1	1	1	100.00
TempTable	ArbitraryColumnName	All CategoryIDs	6	4	4	66.67

赞(0）回复(0）举报 2023-03-28

我来回答

SQL Server Include GROUP BY in query 1 of a UNION, instead of two queries UNIONed to get fill rates/row counts by category id

1条答案

相关问题

热门标签

最新问答