Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    26

    Unanswered: Count the number of groups

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    OK this could work:
    Code:
    SELECT DISTINCT category, count(*) AS product_count
    FROM electronics 
    GROUP BY category 
    UNION ALL
    SELECT NULL, count(DISTINCT category) 
    FROM electronics
    Last edited by madafaka; 11-24-05 at 07:38.

  3. #3
    Join Date
    Oct 2005
    Posts
    26
    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.

  4. #4
    Join Date
    Nov 2002
    Posts
    272

    Red face

    Quote Originally Posted by db__novice
    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.

  5. #5
    Join Date
    Oct 2005
    Posts
    26
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •