Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Unanswered: String equivalent to SUM

    I'm working on an aggregate query where I'd like to SUM not only a numeric field for grouped records, but also concatenate the values from a string field (with some separator like ', ') If I weren't using Access I know I could write a stored proc for this, but surely there's a standard SQL way of doing this?

    The kind of thing I guess I'm after is something like:

    SELECT TaskID, SUM(Days) as TotalEffort, STRSUM(WorkerName, ', ') as Workers From Effort GROUP BY TaskID

    So that records like:

    ID, TaskID, Days, WorkerName
    1, 1, 3, Phil
    2, 1, 4, Phil
    3, 1, 1, John
    4, 2, 2, Mike
    5, 2, 1, Mike
    6, 2, 2, John

    would give:

    TaskID, TotalEffort, Workers
    1, 8, 'Phil, John'
    2, 5, 'Mike, John'

    Any clues?
    Last edited by philmwebb; 06-09-04 at 13:01. Reason: Clarification and examples

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, if you can't write your own function to do it, and if Access doesn't have it built in, then it can't be done. It isn't a "standard" function in the way that SUM is, because SUM is unambiguous and frequently required. By contrast, the output of your STRSUM is ambiguous: should it be 'Phil, John' or 'John, Phil'? If there are 2 'Phil's, should it return 'Phil, Phil' or just 'Phil'? A standard function would have to take additional parameters to tweak all these behaviours.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are engine specific functions that do what you are describing, but there isn't anything that I know of in the SQL standard that will do it. I know that Sybase SQL Anywhere, and PostgreSQL both include functions that will do this.

    It isn't hard to write a user defined function that will handle a specific case, as long as your SQL dialect supports user defined functions. It is more of a challenge to do it generically.

    This kind of processing is really better done on the client side. There are a number of problems that you can encounter if you try to do this on the server side, which is probably why the X3H2 SQL standards committe refused to take any position on the issue.

    -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
  •