Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Posts
    1

    Unanswered: counting rows in select clause

    I would like to query a DB2 table and get all the results of a query in addition to all of the rows returned by the select statement in a separate column.

    E.g., if the table contains columns 'id' and 'user_id', assuming 100 rows, the result of the query would appear in this format: (id) | (user_id) | 100.

    The query below works for obtaining the desired result set; however, i would like something more efficient (perhaps without 2 selects).

    select t.*, g.tally
    from mytable t,
    (select count(*) as tally
    from mytable
    ) as g;


    Is there a function that would be able to achieve this more efficiently? I have tried something like this, but I receive a syntax error for 'over()':

    select t.*, count(*) over() as NumRows
    from table t;

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    What's your DB2 version?
    Which SQL code and message do you get?
    It should work on DB2 for LUW...
    Regards,
    Mark.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If Example 1 not working(syntax error, may be on DB2 iSeries),
    how about Example 2?
    (Though, I don't know wheather Example 2 may work more efficiently than your original query, or not.)

    Example 1:
    Code:
    SELECT t.*
         , COUNT(*) OVER() AS num_rows
     FROM  mytable t
    ;

    Example 2:
    Code:
    SELECT t.*
         , (  ROW_NUMBER()
                 OVER( ORDER BY id      ASC
                              , user_id ASC  )
            + ROW_NUMBER()
                 OVER( ORDER BY id      DESC
                              , user_id DESC )
           ) - 1 AS num_rows
     FROM  mytable t
    ;
    Note: Columns (in ORDER BY) were derived from your description.
    E.g., if the table contains columns 'id' and 'user_id', assuming 100 rows, the result of the query would appear in this format: (id) | (user_id) | 100.

Tags for this Thread

Posting Permissions

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