Selecting COUNT(id) and total in one SQL query

I have a load of items in a table. Each has a user_id.

I would like to 开发者_JAVA技巧find out the number of items per user as well as the total number of items.

If I do this:

SELECT COUNT(id)
FROM items
GROUP BY user_id

...I get the number of items per user but I can't get the total number of items as they are grouped.

Is it possible to do this?

If so, is it a good way of doing things?

Would I be better of summing the counts after selecting them?

The following query will give you an additional row with the total number of items s and user_id=NULL.

SELECT user_id, COUNT(id) n_items
FROM items
GROUP BY user_id WITH ROLLUP

See also the Mysql docs on GROUP BY modifiers.

Something like this...?

SELECT COUNT(*)
FROM items
UNION
SELECT COUNT(id)
FROM items
GROUP BY user_id

Then the first result will be the total and the subsequent results will be the grouped totals.

But personally, I'd just add them up after doing the group by query as you said.