This is SO frustating, I've looked everywhere on the internet and there's nothing I can find that would solve my problem which I'm sure is easily solvable. My database contains the following data:


category | product
-----------------------------
Car Audio | Sony 1XX
Car Audio | Sony 2XX
Car Audio | Sony 3XX
Car Audio | Sony 4XX
Portable Audio | Sony 5XX
Portable Audio | Sony 6XX
Portable Audio | Sony 7XX
Portable Audio | Sony 8XX
Portable Audio | Sony 9XX
Television | Sony 10X
Television | Sony 11X
Television | Sony 12X
Television | Sony 13X
Television | Sony 14X
Television | Sony 15X


Ok, the following select statement returns 3 categories (3 rows) which each contain a number of products. So far so good, now I want to count up the number of categories found, i.e. 3, so I added WITH ROLLUP, but this just adds up the product_count column to 15.

Is it indeed possible to count up the number of rows returned and add that at the end like WITH ROLLUP, or am I wasting my time looking? My database server is MySQL 4.1.


SELECT DISTINCT category, count(*) AS product_count
FROM electronics
GROUP BY category WITH ROLLUP


category | product_count
-----------------------------
Car Audio | 4
Portable Audio | 5
Television | 6
NULL | 15


Many thanks in advance.

OK this could work:

Madafaka, you are a genius, it finally worked. Thank you for taking the time to answer my posts, you were the only one who bothered. Cheers.

No, madafaka was the first one who bothered.
There are many helpful people here, but I'm pretty sure most of them aren't connected to this forum 24/7.

As a matter of fact, I had a few posts in the SQL section over a period of a week or so and madafaka WAS the ONLY person to try and help me in each one, and for that I thank him very much.