#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2005
    Posts
    5
    Rep Power
    0

    How to count the rows of a query result ?


    Maybe I worked to long but I have no clue how to solve this !


    How can I count the number of rows returned by this query ?
    (I need to use this value in a stored procedure)

    SELECT
    AUTO_ID_CUSTOMER
    , SUM(UNIT_QTY*UNIT_PRICE)
    FROM V_TRANSACTIONS
    WHERE INV_DATE > '01/01/2006'
    GROUP BY AUTO_ID_CUSTOMER
    HAVING SUM(UNIT_QTY*UNIT_PRICE) < 100000

    I also tried something like

    select count(distinct v1.auto_id_customer)
    FROM V_TRANSACTIONS v1
    WHERE v1.INV_DATE > '01/01/2006'
    and (select sum(v2.UNIT_QTY*v2.UNIT_PRICE)
    FROM V_TRANSACTIONS v2
    where v2.auto_id_customer = v1.auto_id_customer
    and v2.INV_DATE > '01/01/2006') < 100000

    which works, but unfortunately the execution time on even a small table is not acceptable (> 1 min.)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Originally Posted by FrankBKK
    Maybe I worked to long but I have no clue how to solve this !


    How can I count the number of rows returned by this query ?
    (I need to use this value in a stored procedure)

    SELECT
    AUTO_ID_CUSTOMER
    , SUM(UNIT_QTY*UNIT_PRICE)
    FROM V_TRANSACTIONS
    WHERE INV_DATE > '01/01/2006'
    GROUP BY AUTO_ID_CUSTOMER
    HAVING SUM(UNIT_QTY*UNIT_PRICE) < 100000

    I also tried something like

    select count(distinct v1.auto_id_customer)
    FROM V_TRANSACTIONS v1
    WHERE v1.INV_DATE > '01/01/2006'
    and (select sum(v2.UNIT_QTY*v2.UNIT_PRICE)
    FROM V_TRANSACTIONS v2
    where v2.auto_id_customer = v1.auto_id_customer
    and v2.INV_DATE > '01/01/2006') < 100000

    which works, but unfortunately the execution time on even a small table is not acceptable (> 1 min.)
    Does this run any faster?
    Code:
    SELECT COUNT(*) FROM
    (
    SELECT
      AUTO_ID_CUSTOMER
    , SUM(UNIT_QTY*UNIT_PRICE)
    FROM V_TRANSACTIONS
    WHERE INV_DATE > '01/01/2006'
    GROUP BY AUTO_ID_CUSTOMER
    HAVING SUM(UNIT_QTY*UNIT_PRICE) < 100000
    )
    Clive
  4. #3
  5. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539

IMN logo majestic logo threadwatch logo seochat tools logo