Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: sum of COUNT in group by

    SELECT emp_id,COUNT(*)
    FROM emp
    GROUP BY empId

    THe above query returns the values

    empid COUNT
    1 4
    2 4
    3 5

    BUT i want sum of the count (13) in the same query. How to achieve this.

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The grand total will need to be calculated in a subquery. There are several acceptable ways to do this. Here is one which parsed correctly in Query Analyzer, though I did not execute it against test data:

    SELECT emp_id,COUNT(*), GrandTotal.Total
    FROM emp, (select count(*) Total from emp) GrandTotal
    GROUP BY empId, GrandTotal.Total
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Could you use:
    PHP Code:
    SELECT emp_id,COUNT(*) 
       
    FROM emp 
       GROUP BY empId
       COMPUTE Sum
    (Count(*)) 
    -PatP

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I haven't used COMPUTE much. How does an interface such as Crystal handle the additional data appended to the result set?
    The solution I gave includes the Grand Total with each record. Pat's solution appends the Grand Total to the recordset. Pat's is preferable if you aren't calling your statement from a reporting engine, or if your reporting engine can handle the dataset format.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by blindman
    How does an interface such as Crystal handle the additional data appended to the result set?
    Poorly, of course!

    Most of the "user grade" reporting tools can't cope with a second result set or the descriptive data passed to make the result set make sense. Most of the "enterprise grade" reporting tools do fine with it.

    If COMPUTE helps, that's a good thing. If not, they've already got your solution which should work nicely. I just figured I'd pitch it into the mix in case it might help.

    -PatP

Posting Permissions

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