Robert Mazurowski Robert Mazurowski - 4 days ago 6
SQL Question

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

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?

Answer

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.

Comments