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

    Unanswered: Display the total sum of rows found

    I'm sure there is an easy answer to this question, it's just I couldn't find it in any of the books that I own.

    If I do a SELECT statement that finds say 1000 rows that have been matched, is it possible to return the sum of rows found, i.e. 1000 and at the same time LIMIT the rows returned to 20.

    Basiclly, I want to show the user 20 rows at a time but also the total number of rows found, i.e 1000. I hope I have explained things clear enough.

    Many thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I don't think it's possible in one statement. You have to run 2 statements:
    Code:
    select count(*)from table;
    
    select * from table where rownum <= 20;
    or you can have one additional column in your result filled with the count
    Code:
    select 
    (select count(*) from table),
    t.*
    from table t
    where rownum <= 20;
    or have one additional row, but you have to know how many columns you have in your result. e.g. for 4 columns
    Code:
    selce count(*), count(*), count(*), count(*) from table
    union all
    select * from table
    where rownum <= 20;

  3. #3
    Join Date
    Oct 2005
    Posts
    26
    Thank you very much for your prompt reply.

    With a little bit of tweaking, I managed to get the following statement to work exactly how I like. Basically, out of a table of 3000 rows, it returns 1300 rows, of which I want to display 20 rows only plus at the end of the table one extra row of the count (in this case 1300).

    SELECT product, price from electronics
    WHERE sid = '187'
    LIMIT 20
    UNION ALL
    SELECT count(*), count(*) from electronics
    WHERE sid = '187'

    However, as I want to eventually do a sort on the 1300 rows returned, I think I would like to have an extra column of the count instead of an extra row of the count, i.e. 'product', 'price', 'count'. I know it's inefficient to have an extra colomn all with the same sum but I don't really want to do a sort and then mix the count in with it. A solution to adding an extra column with the count would be much appreciated.

    Or, is it possible to have the above working select statement but with the count as the first row, and then somehow extract the first row using perl before I do a sort on the 1300 rows returned.

    Thanking you again.
    Last edited by db__novice; 11-20-05 at 19:41.

  4. #4
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I'm not sure, but I think, it could work when you first select count(*) then UNION ALL your select ordered by whatever. Do not order final result which includes count.
    Code:
    SELECT count(*), count(*) from electronics
    WHERE sid = '187'
    UNION ALL
    SELECT product, price from electronics
    WHERE sid = '187'
    ORDER BY price DESC
    LIMIT 20      -- I don't know if this goes before ORDER BY

  5. #5
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    now I had a chance to test my last suggestion (previous post). You'll have to use something like:
    Code:
    SELECT count(*) product, count(*) price from electronics
    WHERE sid = '187'
    UNION ALL
    SELECT product, price from 
    (SELECT product, price from electronics
     WHERE sid = '187'
     ORDER BY price DESC
     LIMIT 20      -- I don't know if this goes before ORDER BY
    ) X;
    otherwise most likely your count won't be in first line. the problem is some databases don't allow ORDER BY in views, subqueries, derived tables...

  6. #6
    Join Date
    Oct 2005
    Posts
    26
    Again thank you for your replies. Unfortunatly, I can't seem to get your examples to work. I've managed to come up with the statement below which does exactly what I want i.e. with the count as the first row, this is only possible because I've set the 2nd column of the count row to 0.00, and hopefully as there will never be a product whose price is 0.00 then when I do a ASC sort it should always become the first row.

    Additionally, as the count is a number and is in the product column (products are normally described in words), a ASC sort should always put that as the first row.

    Of course everything falls down if a product is described as a number or it's price is 0.00, rare but could happen.

    SELECT product, price from electronics
    WHERE sid = '187'
    UNION ALL
    SELECT count(*), '0.00' from electronics
    WHERE sid = '187'
    ORDER BY price ASC
    LIMIT 20

    I'm sure there is a proper solution, just haven't found it yet. What I really want is to have an extra column with the count in it, i.e.

    Table has 3000 rows, rows returned from search is 1300

    Product Price Count
    -------------------------------------------
    JVC AV27D202 125.00 1300
    Panasonic CT32SF37 150.00 1300
    Pioneer PDP505HD 200.00 1300
    Sharp 27NS100 250.00 1300
    etc

    instead of

    Product Price
    --------------------------------
    1300 0.00
    JVC AV27D202 125.00
    Panasonic CT32SF37 150.00
    Pioneer PDP505HD 200.00
    Sharp 27NS100 250.00
    etc

    By the way, my database is MySQL. Many thanks.

  7. #7
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    You could use negative price (like -1.00), and NULL description for your count, it should be on the top allways (row solutions).

    did you also try something like:
    Code:
    SELECT 
    product, 
    price,
    (SELECT count(*) from electronics WHERE sid = '187') cnt
    from electronics
    WHERE sid = '187'
    LIMIT 20
    this could be your "column" solution.
    what DB do you use?

  8. #8
    Join Date
    Oct 2005
    Posts
    26
    My database is MySQL. Great idea about the negative price and NULL. I shall use the row solution if nothing else works.

    The column solution however doesn't seem to work, although I can see what you are doing. It falls down I think because it doesn't like the syntax "(SELECT count(*) from electronics WHERE sid = '187') cnt" as a means of creating a third column. Both select statements work perfectly on their own but not together.

    Many thanks.

  9. #9
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    yes, I think problem is: MySQL doesn't support subqueries. Maybe it's not true in newer versions, but I'm sure this feature wasn't supported by some older versions
    Last edited by madafaka; 11-21-05 at 19:29.

Posting Permissions

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