Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Unanswered: concat all col2 values for each col1, and add sum(col3) (was "query help")

    Hi,
    Can anybody help me to create a single query? I have this problem.

    Code:
    CREATE TABLE t1 (
                              col1 VARCHAR(100)
                            , col2 VARCHAR(100)
                            , col3 INT)
    
    INSERT INTO t1 VALUES('A001','Tom',30)
    INSERT INTO t1 VALUES('A001','Rick',40)
    INSERT INTO t1 VALUES('A001','Harry',10)
    
    INSERT INTO t1 VALUES('A002','Peter',50)
    INSERT INTO t1 VALUES('A002','Sam',50)
    
    INSERT INTO t1 VALUES('A003','Fred',50)
    I want a resultset like this ...
    i.e col1 col2(all the values would be represented in a single row for each col1) and sum(col3)

    (Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)

    Code:
    A001  Tom   Rick Harry 80 --sum(col3)
    A002  Peter Sam NULL 100
    A003  Fred  NULL NULL 50
    Any help would be greatly appreciated !!
    Last edited by rudra; 09-11-06 at 17:18.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rudra
    (Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)
    Based on this the below works. I think it is about as efficient as it can be though verbose for the sake of transparency

    Code:
    SET NOCOUNT ON 
    CREATE TABLE t1 (
                              col1 VARCHAR(100)
                            , col2 VARCHAR(100)
                            , col3 INT)
     
    INSERT INTO t1 VALUES('A001','Tom',30)
    INSERT INTO t1 VALUES('A001','Rick',40)
    INSERT INTO t1 VALUES('A001','Harry',10)
    INSERT INTO t1 VALUES('A002','Peter',50)
    INSERT INTO t1 VALUES('A002','Sam',50)
    INSERT INTO t1 VALUES('A003','Fred',50)
    
    SELECT Col1, 
     Col2a, 
     Col2b, 
     Col2c, 
     SUM(Col3) AS TheTotal 
    FROM --Pivot data
      (SELECT TOP 100 PERCENT 
       Col1, 
       (SELECT TOP 1 Col2
       FROM  dbo.t1 AS B 
       WHERE A.Col1 = B.Col1
       ORDER BY
        B.Col2) AS Col2a, 
       (SELECT TOP 1 Col2
       FROM  dbo.t1 AS B 
       WHERE A.Col1 = B.Col1
        AND B.Col2 NOT IN  (SELECT TOP 1 Col2
           FROM  dbo.t1 AS C 
           WHERE C.Col1 = A.Col1
           ORDER BY
            C.Col2)
       ORDER BY 
        B.Col2) AS Col2b, 
       (SELECT TOP 1 Col2
       FROM  dbo.t1 AS B 
       WHERE A.Col1 = B.Col1
        AND B.Col2 NOT IN  (SELECT TOP 2 Col2
           FROM  dbo.t1 AS C 
           WHERE C.Col1 = A.Col1
           ORDER BY
            C.Col2)) AS Col2c, 
       Col3
      FROM  dbo.t1 AS A
      ORDER BY
       Col1, 
       Col2) AS DerT
    GROUP BY
     Col1, 
     Col2a, 
     Col2b, 
     Col2c
     
    DROP TABLE t1 
     
    SET NOCOUNT OFF
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by pootle flump
    Based on this the below works. I think it is about as efficient as it can be though verbose for the sake of transparency
    Awesome, as usual...thanks a ton Pootie!!
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  4. #4
    Join Date
    Aug 2006
    Posts
    5
    nevermind, you have a better solution above.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    As a possible alternative, blindman's neat function here could be adapted, resulting in a much simpler query:

    SELECT col1, dbo.Concat_ICD(col1) as TheNames, Sum(col3) as TheTotal
    FROM t1
    GROUP BY col1

    http://www.dbforums.com/showthread.php?t=1605725

    This would not produce the visible NULL in the result, but I was presuming that wasn't a requirement.
    Paul

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The function is your best solutions, because it works for any number of records.

    By the way, I wish I could take credit for that function, but it is actually one of the many things I have learned from participating in this forum over that past few years.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually that was the solution I hoped to use - it allows n values to be concatenated. However I read the requirement as the return putting the names into three columns rather than one. If this isn't a requirement then defo go with Blindman's solution.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    I wanted it in three different columns.So I used Pootie's one.
    Anyways,Thanks everybody for their valuable info.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    for comparison purposes, here is the equivalent query in mysql --
    Code:
    select col1
         , group_concat(col2)
         , sum(col3)
      from daTable
    group
        by col1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm calling you on that one Rudy. Did you read the requirements carefully?
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...but here is a shorter method of coding it for SQL Server:
    Code:
    SET NOCOUNT ON 
    CREATE TABLE #t1
    	(col1 VARCHAR(100),
    	col2 VARCHAR(100),
    	col3 INT)
     
    INSERT INTO #t1 VALUES('A001','Tom',30)
    INSERT INTO #t1 VALUES('A001','Rick',40)
    INSERT INTO #t1 VALUES('A001','Harry',10)
    INSERT INTO #t1 VALUES('A002','Peter',50)
    INSERT INTO #t1 VALUES('A002','Sam',50)
    INSERT INTO #t1 VALUES('A003','Fred',50)
    
    select	A.col1,
    	min(A.col2) as name1,
    	min(B.col2) as name2,
    	min(C.col2) as name3,
    	max(coalesce(A.col3, 0) + coalesce(B.col3, 0) + coalesce(C.col3, 0)) as col3total
    from	#t1 A
    	left outer join #t1 B on A.col1 = B.col1 and A.col2 < B.col2
    	left outer join #t1 C on B.col1 = C.col1 and B.col2 < C.col2
    group by A.col1
    
    drop table #t1
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    I'm calling you on that one Rudy. Did you read the requirements carefully?
    oh, SHEEEEEEEESH, okay
    Code:
    select col1
         , group_concat(col2 separator ' ')
         , sum(col3)
      from t1
    group
        by col1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    for comparison purposes, here is the equivalent query in mysql --
    Code:
    select col1
         , group_concat(col2)
         , sum(col3)
      from daTable
    group
        by col1

    ...and that would be just great if it was a real ANSI compliant databa...

    Oh, never mind
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pot? meet kettle

    kettle? meet pot

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    That was great,thank you Blindman.And thank you all of you for your help.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

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