Getting total from query of individual groups

I’ running a query that returns total count of offices per county.

SELECT county, COUNT(county) AS cnt
FROM state
GROUP BY county
ORDER BY county ASC

and my results look like this:

"Anderson"  "138"
"Andrews"   "17"
"Angelina"  "80"
"Aransas"   "56"
"Archer"    "38"

Is there a way to get the total count returned in the same query?

Answer

If you want it as a column, use a window function:

SELECT county, COUNT(*) AS cnt,
       SUM(COUNT(*)) OVER () as total
FROM state
GROUP BY county
ORDER BY county ASC;

If you want it as an additional row, then use WITH ROLLUP:

SELECT county, COUNT(*) AS cnt
FROM state
GROUP BY county WITH ROLLUP;