Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Sep 2009
    Posts
    90

    Unanswered: sum of all columns and new columns

    i hope you can help me with my sql problem.
    i want to get the sum of all the columns in a table like this one:
    select sum(*) as * from db

    but of course, this one is not possible.

    so can you help me find other ways to come up with this result?

    thnx in advance!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    SELECT SUM(ColumnA) + SUM(ColumnB) + ... AS totalSum
    FROM MyTable
    Is this what you are looking for?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2009
    Posts
    90

    re:

    Quote Originally Posted by Wim
    Code:
    SELECT SUM(ColumnA) + SUM(ColumnB) + ... AS totalSum
    FROM MyTable
    Is this what you are looking for?
    thanks for your reply!
    but this is not the one that i need.

    for example i have this table:

    Date Name item1 item2 item3
    ... a 500 200 100
    ... b 100 100 100
    ... c 100 310 400
    ... Total 700 610 600

    I want to get the values for 'Total' without specifying the column name in the sum function because the user can add columns. There may be item4,item5, etc... I want it to automatically sum up the columns without specifying the column names. Maybe Dynamic sql will do, but i don't know how.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    it would probably work better with a better table design. something like date, name, item# and amt. Then you could select item#, count(*) ... group by item#
    Dave

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This is an adopted version of the very first SP I wrote for SQL Server (a SP to automatically generate audit tables).

    It excludes all the PK columns from the summarised columns (what would be the meaning of SUM(Id) ?).
    However, it does not yet exclude FK's from being summarised. Though they should be excluded too, for the very same reason (meaning SUM(ColorCodeId)?). I leave that as an exercise for the interested reader
    Code:
    Use AdventureWorks
    
    DROP FUNCTION dbo.fnIsColumnPrimaryKey
    GO
    
    CREATE FUNCTION dbo.fnIsColumnPrimaryKey
        (@sTableName varchar(128), @sColumnName varchar(128))
    RETURNS bit
    AS
    BEGIN
        DECLARE    @nTableID int,
                   @nIndexID int,
                   @i int
        
        SET @nTableID = OBJECT_ID(@sTableName)
    
    	SELECT @nIndexID = indid
    	FROM      sysindexes
    	WHERE  id = @nTableID
    	 AND      indid BETWEEN 1 And 254 
    	 AND      (status & 2048) = 2048
    	ORDER BY indid
    
    	IF (@nIndexID  Is Null)
    		RETURN 0
    
        IF @sColumnName IN
            (SELECT sc.[name]
              FROM       sysindexkeys sik
              INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid =
                         sc.colid
              WHERE      sik.id = @nTableID
              AND        sik.indid = @nIndexID        )
         BEGIN
            RETURN 1
         END
    
        RETURN 0
    END
    GO
    
    
    DROP PROCEDURE dbo.p_GenerateSumOfAllColumns
    GO
    
    CREATE PROCEDURE dbo.p_GenerateSumOfAllColumns
    	@Owner varchar(128),
    	@TableName varchar(128),
    	@Debug bit = 0
    AS
    BEGIN
    	SET NOCOUNT ON
    
    	-- variables
    	DECLARE @ColumnName varchar(128)
    	DECLARE @ColumnType varchar(128)
    	DECLARE @FullTableName varchar(255)
    	-- Declare variables to build SQL statement
    	DECLARE @SQLStatement varchar(8000)
    
    	-- constants
    	DECLARE @CRLF varchar(4)
    	SET @CRLF = CHAR(13) + CHAR(10)
    
    	DECLARE @ORIGINAL_SQL_STATEMENT varchar(8000)
    	SET @ORIGINAL_SQL_STATEMENT = 'SELECT '
    
    	if @Debug = 1 PRINT '-- DEBUG ON: generated SQL statements are only printed, not executed. You can execute the SQL commands interactive one by one.'
    
    	-- remove any present '[' or ']'
    	SET @Owner = replace(replace(@Owner, '[', ''), ']', '')
    	SET @TableName = replace(replace(@TableName, '[', ''), ']', '')
    
    	-- Check @Owner, @TableName
    	IF @Owner is null OR @TableName is null
    	BEGIN
    		PRINT 'ERROR: @Owner and @TableName cannot be NULL'
    		RETURN
    	END
    
    	SET @FullTableName = '[' + @Owner + '].[' + @TableName + ']'
    
    	-- Check if table exists
    	IF not exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'' + @FullTableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    	BEGIN
    		PRINT 'ERROR: Table ' + @FullTableName + ' does not exist'
    		RETURN
    	END
    
    	SET @SQLStatement = @ORIGINAL_SQL_STATEMENT
    
    	-- Declare cursor to loop over columns
    	DECLARE TableColumns CURSOR Read_Only FOR 
    	SELECT c.name, s.name AS TypeName
    	FROM dbo.sysobjects AS o
    		INNER JOIN dbo.syscolumns AS c ON 
    			c.id = o.id
    		INNER JOIN dbo.systypes AS s ON 
    			c.xtype = s.xtype and
    			c.xusertype = s.xusertype
    	WHERE o.type = 'U' and
    		o.id = object_id(N'' + @FullTableName)
    	order by c.colId
    
    	OPEN TableColumns
    
    	FETCH Next FROM TableColumns
    	INTO @ColumnName, @ColumnType
    	
    	WHILE @@FETCH_STATUS = 0
    	BEGIN
    		-- only calculate sums for "number" columns
    		IF @ColumnType in ('decimal', 'numeric', 'int', 'smallint', 'tinyint', 'money', 'smallmoney')
    			-- We explicitly look for PK columns to excude them from the summarised columns, 
    			--	'cuz what would be the meaning of SUM(Id)?
    			IF dbo.fnIsColumnPrimaryKey(@FullTableName, @ColumnName) = 0
    			BEGIN
    				-- if the current column is not the first column in @SQLStatement,
    				--	 end the previous column with ', ' + CRLF
    				IF @SQLStatement <> @ORIGINAL_SQL_STATEMENT
    					SET @SQLStatement = @SQLStatement + ', ' + @CRLF + '       '
    
    				SET @SQLStatement = @SQLStatement + 'SUM([' + @ColumnName + '])' + ' AS [Sum' + @ColumnName + ']'
    			END
    		
    		FETCH Next FROM TableColumns
    		INTO @ColumnName, @ColumnType
    	END
    	
    	CLOSE TableColumns
    	DEALLOCATE TableColumns
    
    	SET @SQLStatement = @SQLStatement + @CRLF + 'FROM ' + @FullTableName + @CRLF
    
    	if @Debug = 1 PRINT @SQLStatement ELSE EXEC (@SQLStatement)
    END;
    GO
    
    EXECUTE dbo.p_GenerateSumOfAllColumns @Owner = 'Sales', @TableName = 'SalesOrderDetail', @Debug = 0
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Sep 2009
    Posts
    90
    Quote Originally Posted by Wim
    This is an adopted version of the very first SP I wrote for SQL Server (a SP to automatically generate audit tables).

    It excludes all the PK columns from the summarised columns (what would be the meaning of SUM(Id) ?).
    However, it does not yet exclude FK's from being summarised. Though they should be excluded too, for the very same reason (meaning SUM(ColorCodeId)?). I leave that as an exercise for the interested reader
    Code:
    Use AdventureWorks
    
    DROP FUNCTION dbo.fnIsColumnPrimaryKey
    GO
    
    CREATE FUNCTION dbo.fnIsColumnPrimaryKey
        (@sTableName varchar(128), @sColumnName varchar(128))
    RETURNS bit
    AS
    BEGIN
        DECLARE    @nTableID int,
                   @nIndexID int,
                   @i int
        
        SET @nTableID = OBJECT_ID(@sTableName)
    
    	SELECT @nIndexID = indid
    	FROM      sysindexes
    	WHERE  id = @nTableID
    	 AND      indid BETWEEN 1 And 254 
    	 AND      (status & 2048) = 2048
    	ORDER BY indid
    
    	IF (@nIndexID  Is Null)
    		RETURN 0
    
        IF @sColumnName IN
            (SELECT sc.[name]
              FROM       sysindexkeys sik
              INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid =
                         sc.colid
              WHERE      sik.id = @nTableID
              AND        sik.indid = @nIndexID        )
         BEGIN
            RETURN 1
         END
    
        RETURN 0
    END
    GO
    
    
    DROP PROCEDURE dbo.p_GenerateSumOfAllColumns
    GO
    
    CREATE PROCEDURE dbo.p_GenerateSumOfAllColumns
    	@Owner varchar(128),
    	@TableName varchar(128),
    	@Debug bit = 0
    AS
    BEGIN
    	SET NOCOUNT ON
    
    	-- variables
    	DECLARE @ColumnName varchar(128)
    	DECLARE @ColumnType varchar(128)
    	DECLARE @FullTableName varchar(255)
    	-- Declare variables to build SQL statement
    	DECLARE @SQLStatement varchar(8000)
    
    	-- constants
    	DECLARE @CRLF varchar(4)
    	SET @CRLF = CHAR(13) + CHAR(10)
    
    	DECLARE @ORIGINAL_SQL_STATEMENT varchar(8000)
    	SET @ORIGINAL_SQL_STATEMENT = 'SELECT '
    
    	if @Debug = 1 PRINT '-- DEBUG ON: generated SQL statements are only printed, not executed. You can execute the SQL commands interactive one by one.'
    
    	-- remove any present '[' or ']'
    	SET @Owner = replace(replace(@Owner, '[', ''), ']', '')
    	SET @TableName = replace(replace(@TableName, '[', ''), ']', '')
    
    	-- Check @Owner, @TableName
    	IF @Owner is null OR @TableName is null
    	BEGIN
    		PRINT 'ERROR: @Owner and @TableName cannot be NULL'
    		RETURN
    	END
    
    	SET @FullTableName = '[' + @Owner + '].[' + @TableName + ']'
    
    	-- Check if table exists
    	IF not exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'' + @FullTableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    	BEGIN
    		PRINT 'ERROR: Table ' + @FullTableName + ' does not exist'
    		RETURN
    	END
    
    	SET @SQLStatement = @ORIGINAL_SQL_STATEMENT
    
    	-- Declare cursor to loop over columns
    	DECLARE TableColumns CURSOR Read_Only FOR 
    	SELECT c.name, s.name AS TypeName
    	FROM dbo.sysobjects AS o
    		INNER JOIN dbo.syscolumns AS c ON 
    			c.id = o.id
    		INNER JOIN dbo.systypes AS s ON 
    			c.xtype = s.xtype and
    			c.xusertype = s.xusertype
    	WHERE o.type = 'U' and
    		o.id = object_id(N'' + @FullTableName)
    	order by c.colId
    
    	OPEN TableColumns
    
    	FETCH Next FROM TableColumns
    	INTO @ColumnName, @ColumnType
    	
    	WHILE @@FETCH_STATUS = 0
    	BEGIN
    		-- only calculate sums for "number" columns
    		IF @ColumnType in ('decimal', 'numeric', 'int', 'smallint', 'tinyint', 'money', 'smallmoney')
    			-- We explicitly look for PK columns to excude them from the summarised columns, 
    			--	'cuz what would be the meaning of SUM(Id)?
    			IF dbo.fnIsColumnPrimaryKey(@FullTableName, @ColumnName) = 0
    			BEGIN
    				-- if the current column is not the first column in @SQLStatement,
    				--	 end the previous column with ', ' + CRLF
    				IF @SQLStatement <> @ORIGINAL_SQL_STATEMENT
    					SET @SQLStatement = @SQLStatement + ', ' + @CRLF + '       '
    
    				SET @SQLStatement = @SQLStatement + 'SUM([' + @ColumnName + '])' + ' AS [Sum' + @ColumnName + ']'
    			END
    		
    		FETCH Next FROM TableColumns
    		INTO @ColumnName, @ColumnType
    	END
    	
    	CLOSE TableColumns
    	DEALLOCATE TableColumns
    
    	SET @SQLStatement = @SQLStatement + @CRLF + 'FROM ' + @FullTableName + @CRLF
    
    	if @Debug = 1 PRINT @SQLStatement ELSE EXEC (@SQLStatement)
    END;
    GO
    
    EXECUTE dbo.p_GenerateSumOfAllColumns @Owner = 'Sales', @TableName = 'SalesOrderDetail', @Debug = 0

    Thanks WIM! But this one is so complicated. Is there a much easier way?
    Im using it with vb.net...

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by rvr707
    Thanks WIM! But this one is so complicated. Is there a much easier way?
    Easier than copying and pasting the solution and using it? Hmm, let me think ... No, I don't think so.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by rvr707
    i hope you can help me with my sql problem.
    i want to get the sum of all the columns in a table like this one:
    select sum(*) as * from db

    but of course, this one is not possible.

    so can you help me find other ways to come up with this result?

    thnx in advance!
    you can do it but it requires querying the system tables that contain all the definitions and usually its a case of if you don't know how to do that already then you don't know enough to safely play with them.

    i think instead the problem is that your database needs Normalizing more if your not sure how to do than then there is a fairly good guide to the basics at Database Normalization Basics
    Last edited by m.timoney; 09-30-09 at 12:53.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by m.timoney
    .. i think instead the problem is that your database needs Normalizing more ..
    I totally agree.

    You are asking for a simple solution for a dynamic and complex problem. If your company needs a system that is so dynamic that it allows users to add columns at run time, you will have to write code that will dynamically query the table structure to figure out how the table is defined the very moment it is queried. Such code is inherently complex.

    I have never came upon a situation where my users needed to add columns at will. Can you describe this a little bit so we can better understand the nature and the need for this requirement? Better normalisation could indeed solve your problem.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    hold on
    Quote Originally Posted by rvr707
    Im using it with vb.net...
    in that case build the query dynamically,

    ie

    Code:
    Selectstring = "Select 0"
    
    Foreach DataColumn dc in table.Columns
    Selectstring += " + Sum(" + dc.ColumnName + ")" 
    Next
    Selectstring += "From ....
    then run that string as the query
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  11. #11
    Join Date
    Sep 2009
    Posts
    90
    THANK YOU SO MUCH FOR ALL YOUR HELP!

    Here is the reason why it needs to add columns at runtime:
    @Wim Just like your reply to me on my other post that requires me to verify the data, it has something to do with this one.

    I have a table that has sales report, inside that table, there are products (e.g. 0033,0044,0055)

    Code:
    MDC Code----0033----------0044---------0055---------TOTAL
    123----------100.5----------400----------100----------600.5
    456----------200------------500----------100----------800
    789----------300------------600----------100----------1000
    TOTAL-------600.5----------1600---------300----------2400.5
    There will be a tendency that the user will add products so he will be adding another column for that new product.

    The reason why I ask how to add this columns is to verify if the TOTAL VALUES are correct. Just to verify if the total is not wrong.

    I hope you get it and I really need your suggestions.

    Thanks a lot!
    Last edited by rvr707; 09-30-09 at 13:17.

  12. #12
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    well then that shouldn't be a table, you should normalise the structure then build the report from the data

    so you have your Product table that contains
    Product ID, and anyother product information

    your MDC Table that has your MDC codes

    and your data table that contains
    MDC Code, Product ID, Value

    you then group on the MDC and do the sum

    it's because doing that sort of thing is horrible in a relational database that we have normalisation
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  13. #13
    Join Date
    Sep 2009
    Posts
    90
    can you site me a code example?

    I'm sorry this is the first time I encountered this problem.

    Thanks a lot!

    (I'm using it with vb.net)

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rvr707
    because the user can add columns.

    Now that's the funniest damn thing I've heard in a while....

    Can they do

    EXEC(DROP db)

    ???
    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.

  15. #15
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What does MDC stand for ? Major Diagnostic Category?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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