MS ACCESS SQL How to UNION with Group By displaying Grand Total

Question

I have a query where I want to group by to show count of amount of each value in a filed and would like to UNION a second query which Should show Grand Total and Count of All items. Here is the abstract of my query.

SELECT a.[Field to Count], Count(a.[Field to Count]) as Count
FROM MyTable as a
Where a.[Some Field] = "Value1" AND a.[Some other Field] <> "Some value"
Group By a.[Field to Count]
UNION
SELECT "Grand Total", Count(*)
FROM MyTable as a
Where a.[Some Field] = "Value1" AND a.[Some other Field] <> "Some value"
Group By ?

What is want is this:
Field to Count ========= Count
Value1 ================ Count of Value1
Value2 ================ Count of Value2
Value3 ================ Count of Value3
"Grand Total" ========= Count of All Field to Count Values

I know that without the Group By in the Second query it will not display anything so I know I need to use this. Basically something like "Group By *"

Any suggestions?

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2016-11-29T10:51:31

I'm not sure you need GROUP BY in the second query or if that would even make sense. Instead, just select the total table count:

SELECT t.label, t.count
FROM
(
    SELECT a.[Field to Count] AS label,
           COUNT(*) AS count,
           0 AS pos
    FROM MyTable as a
    WHERE a.[Some Field] = "Value1" AND a.[Some other Field] <> "Some value"
    GROUP BY a.[Field to Count]
    UNION
    SELECT "Grand Total" AS label,
           COUNT(*) AS count,
           1 AS pos
    FROM MyTable as a
    WHERE a.[Some Field] = "Value1" AND a.[Some other Field] <> "Some value"
) t
ORDER BY t.pos, t.label

Note that I added a computed column pos to each subquery in the UNION to be able to order the grand total last. Note that in the outer query I don't actually select pos because we don't want it in the result set, we only want it for ordering.

MS ACCESS SQL How to UNION with Group By displaying Grand Total

2 Answers