Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    14

    Unanswered: How to group similar column name and sum the similar column name together

    Hey Gurus,

    I have a problem on getting the sql statement which will group similar column name and sum their number together(another column).


    A million thanks in advance.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    select sum(col1 + col2) from mytable

    ??
    Last edited by jezemine; 04-10-08 at 04:16.

  3. #3
    Join Date
    May 2004
    Posts
    14
    Hey Jezemine,

    Thank you for your immediate response. What I mean is that the column name has similar name like test1, test2. How do I select dynamically to show that the result of the similar name and sum of the similar column name?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, that type of similar name

    before you can select dynamically, you have to change your table to first normal form so that those columns go away

    easy, eh?

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

  5. #5
    Join Date
    Feb 2008
    Location
    Columbus, OH
    Posts
    26
    I'm not sure I understand if this is what you're trying to accomplish or not, but ...

    Code:
    IF EXISTS (SELECT * FROM sysobjects WHERE name='SumItUp') DROP TABLE dbo.SumItUp
    GO
    
    CREATE TABLE dbo.SumItUp
    	(SumItUpID	INT,
    	col1		INT,
    	col2		INT,
    	col3		INT,
    	c0l4		INT)
    
    GO
    
    INSERT INTO dbo.SumItUp (SumItUpID, col1, col2, col3, c0l4) 
    	SELECT 1, 1, 2, 3, 4
    	UNION
    	SELECT 2, 2, 3, 4, 5
    	UNION 
    	SELECT 3, 3, 4, 5, 6
    
    GO
    
    DECLARE @DelimitedString 	VARCHAR(500)
    DECLARE @SQLStr			VARCHAR(500)
    
    -- Create a temp table to hold the column names
    CREATE TABLE dbo.#ColumnsToSum
    	(columnname	VARCHAR(40))
    
    -- Populate with desired column names
    INSERT INTO dbo.#ColumnsToSum
    SELECT
    	syscolumns.[name]
    FROM syscolumns
    INNER JOIN sysobjects ON syscolumns.[id]=sysobjects.[id]
    WHERE syscolumns.[name] LIKE 'col%'
    AND sysobjects.[name] = 'SumItUp'
    
    -- Codes shamelessly stolen from blindman
    -- http://sqlblindman.googlepages.com/creatingcomma-delimitedstrings
    SELECT  @DelimitedString = ISNULL(@DelimitedString + ' + ', '') + columnname
    FROM    dbo.#ColumnsToSum
    
    SET @SQLStr = 'SELECT SUM(' + @DelimitedString + ') FROM dbo.SumItUp GROUP BY SumItUpID'
    
    EXEC(@SQLStr)
    
    DROP TABLE dbo.#ColumnsToSum

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Apart from the CSV list of columns rather than using a "+" I think it is.

    Whather or not what he wants is what he should want is a different question....
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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