Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2008
    Posts
    6

    Unanswered: Summing a count field

    I am running the following sql query:

    db2 "select (integer(length(message))), count(*) from db2.logging where date(datestamp) = '2008-09-01' AND (integer(length(message))) BETWEEN 1000 AND 5000 GROUP BY length(message)"

    I am getting a count of the messages as expected e.g.:

    1 2
    --------- -----------
    1000 5
    1005 2
    1110 50

    However, what I want is the sum of count field (2) i.e. in the above example 57 - is there any way of doing this?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use a CTE (Common table expression):

    with t1 (msg_len,quantity) (
    select (integer(length(message))), count(*) from db2.logging where date(datestamp) = '2008-09-01' AND (integer(length(message))) BETWEEN 1000 AND 5000 GROUP BY length(message)
    ) select sum(quantity) from t1

    Andy

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    just omit the group by:

    db2 "count(*) from db2.logging where date(datestamp) = '2008-09-01' AND (integer(length(message))) BETWEEN 1000 AND 5000"

Posting Permissions

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