Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    52

    Unanswered: counting rows in a group by

    Hi all friends


    there is a query like this

    SELECT
    T1.id
    FROM
    sysobjects T1, syscomments T2
    WHERE
    T1.id = T2.id
    AND T1.type = 'P'
    AND UPPER(T2.text) LIKE '%EXISTS%'
    GROUP BY T1.id

    I want to count the result of the query, how can i do this ????


    Greetings!!!

  2. #2
    Join Date
    Nov 2002
    Posts
    207
    SELECT count(T1.id)
    FROM sysobjects T1, syscomments T2
    WHERE T1.id = T2.id
    AND T1.type = 'P'
    AND UPPER(T2.text) LIKE '%EXISTS%'
    GROUP BY T1.id

    will give number of lines per SP with 'EXISTS' clause based on T1.id
    If you want to count total number of lines with 'EXISTS' claues then use...

    SELECT count(T1.id)
    FROM sysobjects T1, syscomments T2
    WHERE T1.id = T2.id
    AND T1.type = 'P'
    AND UPPER(T2.text) LIKE '%EXISTS%'

    If you are looking for something else, pls. provide more details.

    HTH.

  3. #3
    Join Date
    Nov 2003
    Posts
    52
    Hi mkalsi:

    you say the query will give me the total number of rows, but there are repeated rows which i dont wanna count

    SELECT count(T1.id)
    FROM sysobjects T1, syscomments T2
    WHERE T1.id = T2.id
    AND T1.type = 'P'
    AND UPPER(T2.text) LIKE '%EXISTS%'


    So how can i count this????

    DISTINCT and COUNT i think dont work together



    ThanX!!!!

  4. #4
    Join Date
    Nov 2002
    Posts
    207
    It works...

    SELECT distinct count(T1.id)
    FROM sysobjects T1, syscomments T2
    WHERE T1.id = T2.id
    AND T1.type = 'P'
    AND UPPER(T2.text) LIKE '%EXISTS%'

  5. #5
    Join Date
    Nov 2003
    Posts
    52
    Ooohhh yeah!!!!!

    I was trying like this but it is very wrong

    SELECT count(distinct T1.id) -- <-- not correct
    FROM sysobjects T1, syscomments T2
    WHERE T1.id = T2.id
    AND T1.type = 'P'
    AND UPPER(T2.text) LIKE '%EXISTS%'

    ThanX!!!!!

Posting Permissions

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