x

How to count and sum values of multiple column

Input table![Result looking for

input.jpg (80.9 kB)
output.jpg (36.5 kB)
more ▼

asked Mar 14, 2015 at 02:24 PM in Default

avatar image

nara
41 1 1 7

Guess i already have a input table imported from excel.. how do i take it forward from there?

Mar 16, 2015 at 06:45 AM nara
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

Using the fancy group by and rollup functions with a combination of unpivot and pivot makes this a bit simpler. This is run on John's sample table generated above.

 -- unpivot, then "count" by summing a derived counter
 ; with cte as (
     select * 
         , SumValue = 1
     from (
         select *
         from #test1
         ) src
     unpivot (
         StatusValue for StatusColumn in (
             status1, status2, status3, status4, status5
             )
         ) upvt
     )
 select isnull(StatusColumn,'Total')
     , Verified = sum(isnull([Verified],0))
     , Added = sum(isnull([Added],0))
     , Changed = sum(isnull([Changed],0))
     , [www.xyz.com] = sum(isnull([www.xyz.com],0))
     , [www.abc.com] = sum(isnull([www.abc.com],0))
 from (
     select StatusColumn, StatusValue, SumValue 
     from cte
     ) src
 pivot (
     sum(SumValue) for StatusValue in (
         [Verified],[Added],[Changed],[www.xyz.com],[www.abc.com]
         )
     ) pvt
 group by rollup (StatusColumn)

Note that in the final select, I sum the columns coming out of the pivot, even though this isn't necessary for the results - it is though required to allow me to use the group by rollup option.

Also note that this solution is only valid for 2008 onwards.

John's point about this being a pain to manage is spot on. It could be made dynamic with a bit of work though.

Edit: Here's a dynamic version. As requested, it doesn't rely on specific values in the status fields. It does though force URL columns to display to the right of non-URL columns. Within the two column groups, columns will be alpha sorted left to right by name. It's simple enough to take this out if you don't need it.

 -- dynamic version
 
 declare @select_cols varchar(max)
     , @pivot_cols varchar(max)
 
 -- get the column lists 
 ; with cte as (
     select distinct StatusValue
         -- we use this to force column ordering to show URL values to the right of non-URL values
         , SortKey = case when left(StatusValue,4) = 'www.' then 2 else 1 end
     from (
         select *
         from #test1
         ) src
     unpivot (
         StatusValue for StatusColumn in (
             status1, status2, status3, status4, status5
             )
         ) upvt
     )
 select @select_cols = stuff(
                             (
                             select ',[' + StatusValue + '] = sum(isnull([' + StatusValue + '],0))' 
                             from cte 
                             where StatusValue <> ''
                             order by SortKey, StatusValue
                             for xml path('')
                             )
                             , 1, 0, '') 
     , @pivot_cols = stuff(
                             (
                             select '[' + StatusValue + '],' 
                             from cte 
                             where StatusValue <> ''
                             order by SortKey, StatusValue
                             for xml path('')
                             )
                             , 1, 0, '') 
 from cte
 
 -- substitute the column lists into the query
 declare @sql varchar(max)
 
 select @sql = '; with cte as (
     select * 
         , SumValue = 1
     from (
         select *
         from #test1
         ) src
     unpivot (
         StatusValue for StatusColumn in (
             status1, status2, status3, status4, status5
             )
         ) upvt
     )
 select isnull(StatusColumn,''Total'')' + @select_cols 
 + ' from (
     select StatusColumn, StatusValue, SumValue 
     from cte
     ) src
 pivot (
     sum(SumValue) for StatusValue in (' + left(@pivot_cols,len(@pivot_cols)-1) +
         ')
     ) pvt
 group by rollup (StatusColumn)'
 
 -- show the resulting query
 print @sql
 
 -- show the query result
 exec(@sql)
more ▼

answered Mar 16, 2015 at 10:22 AM

avatar image

iainrobertson
3k 4 7 11

This is more elegant than what I came up with. ;-) PIVOT/UNPIVOT is definitely NOT one of my stronger skills. ;-)

Mar 16, 2015 at 11:36 AM JohnM

Awesome.. This works :-) .. I have a quick question.. What if a new values suddenly shows up,e.g. 'www.somewhere.com', 'some vaule' how do i get that in another column then?

Mar 16, 2015 at 11:38 AM nara

This is where the problem lies with this approach. You have to explicitly provide the values for the column headers. I'll see if I can make it dynamic, give me a while...

Mar 16, 2015 at 11:51 AM iainrobertson

Can we get the distinct values? and then sum. instead of specifying as Verified, Added, Changed.

Mar 16, 2015 at 11:54 AM nara
(comments are locked)
10|1200 characters needed characters left

There are fancy functions like group by rollup and things that might can do this for you, however I am not a heavy TSQL developer type guy. Going back old school how I solved for this many years ago was doing a UNION with sums. This code should get you the end result but there may be more proper ways or efficient ways of doing the same thing.

SELECT ATTRIBUTE, VERIFIED, ADDED, CHANGED, [WWW.XYZ.COM], [WWW.ABC.COM] FROM dbo.[Table]

UNION SELECT 'TOTALS', SUM(VERIFIED), SUM(ADDED), SUM(CHANGED), SUM([WWW.XYZ.COM]), SUM([WWW.ABC.COM]) FROM dbo.[table]

more ▼

answered Mar 14, 2015 at 02:52 PM

avatar image

Tim
40.9k 39 95 168

Thanks Tim.. however the query did not throw the result expected. I believe the distinct value in each column should be counted first and then sum.

Mar 15, 2015 at 12:41 PM nara

Ah, didn't realize you could have more than one status1, status2, etc. In that case you need to sum each column and do a group by attribute such as

SELECT ATTRIBUTE, SUM(VERIFIED), SUM(ADDED), SUM(CHANGED), SUM([WWW.XYZ.COM]), SUM([WWW.ABC.COM]) FROM dbo.[Table] GROUP BY Attribute

UNION SELECT 'TOTALS', SUM(VERIFIED), SUM(ADDED), SUM(CHANGED), SUM([WWW.XYZ.COM]), SUM([WWW.ABC.COM]) FROM dbo.[table]

Mar 16, 2015 at 10:25 AM Tim
(comments are locked)
10|1200 characters needed characters left

I'm guessing there is a much more elegant solution to this, however, I did it in this fashion:

 CREATE TABLE #test1 (name VARCHAR(25), status1 VARCHAR(15), status2 VARCHAR(15), status3 VARCHAR(15), status4 VARCHAR(15), status5 VARCHAR(15))
 
 INSERT #test1 (name, status1, status2, status3, status4, status5)
     SELECT 'Arjun', 'Changed', 'Verified', 'Verified', 'Added', 'www.abc.com' UNION
     SELECT 'Arun', 'www.abc.com', 'Changed', 'Added', 'Added','Changed' UNION
     SELECT 'Ashok', 'Verified', 'Verified', 'Changed', 'Changed', 'Changed' UNION
     SELECT 'Divya', 'www.xyz.com', 'added', 'verified', 'www.abc.com', 'verified' UNION
     SELECT 'John', 'Verified', 'Changed', 'Added', 'Verified', 'Verified' UNION
     SELECT 'Manoj', 'Added', 'Verified', 'www.abc.com','Changed', 'Changed' UNION
     SELECT 'Ranjitha', 'Added', 'Added', 'Verified', 'Verified', 'Verified' UNION
     SELECT 'Usha', '', 'Verified', 'Changed', 'Changed', 'Changed' UNION
     SELECT 'Xavier', 'Added', 'www.abc.com', 'Added', 'Verified', 'Verified'
 
 CREATE TABLE #results (a char(1) default 1, statustxt varchar(15),Verified INT, added INT, changed INT, [www.xyz.com] INT , [www.abc.com] INT) 
 
 INSERT INTO #results (statustxt, Verified, added, changed, [www.xyz.com], [www.abc.com])
     SELECT 'status1', [Verified], [Added], [Changed], [www.xyz.com], [www.abc.com] 
     FROM 
        (SELECT status1
           FROM #test1
           ) AS sourcetable
        PIVOT
           (COUNT(status1)
           FOR status1 in ([Verified], [Added], [Changed], [www.xyz.com], [www.abc.com])
           ) as pt1
     UNION
     SELECT 'status2', [Verified], [Added], [Changed], [www.xyz.com], [www.abc.com] 
     FROM 
        (SELECT status2
           FROM #test1
           ) AS sourcetable
        PIVOT
           (COUNT(status2)
           FOR status2 in ([Verified], [Added], [Changed], [www.xyz.com], [www.abc.com])
           ) as pt1
     UNION
     SELECT 'status3', [Verified], [Added], [Changed], [www.xyz.com], [www.abc.com] 
     FROM 
        (SELECT status3
           FROM #test1
           ) as sourcetable
        PIVOT
           (COUNT(status3)
           FOR status3 in ([Verified], [Added], [Changed], [www.xyz.com], [www.abc.com])
           ) as pt1
     UNION
     SELECT 'status4', [Verified], [Added], [Changed], [www.xyz.com], [www.abc.com] 
     FROM 
        (SELECT status4
           FROM #test1
           ) as sourcetable
        PIVOT
           (COUNT(status4)
           FOR status4 in ([Verified], [Added], [Changed], [www.xyz.com], [www.abc.com])
           ) as pt1
     UNION
     SELECT 'status5', [Verified], [Added], [Changed], [www.xyz.com], [www.abc.com] 
     FROM 
        (SELECT status5
           FROM #test1
           ) as sourcetable
        PIVOT
           (COUNT(status5)
           FOR status5 in ([Verified], [Added], [Changed], [www.xyz.com], [www.abc.com])
           ) as pt1
 
    SELECT statustxt, Verified, added, changed, [www.xyz.com],[www.abc.com] FROM #results
    UNION
    SELECT 'total', SUM(verified), SUM(added), SUM(changed), SUM([www.xyz.com]), SUM([www.abc.com])
    FROM #results
    GROUP BY a 

One of the beauties of T-SQL is there is always another way to get things done. ;-) This solution, however, is NOT dynamic so if you have additional columns or status values, this type of coding will become very ugly very quickly I would imagine. I could also be way off base in this solution.

If there is a better way, please someone post so that I can learn too!

Hope that helps some!

more ▼

answered Mar 15, 2015 at 04:04 PM

avatar image

JohnM
14.5k 3 7 15

(comments are locked)
10|1200 characters needed characters left

New Input File

New Output Result

This is a question by the way.. How to count and sum values of multiple column - Team wise

input1.jpg (31.9 kB)
output1.jpg (43.6 kB)
more ▼

answered Apr 06, 2015 at 08:43 AM

avatar image

nara
41 1 1 7

(comments are locked)
10|1200 characters needed characters left

Hi #iainrobertson,

I have a similar question posted in a separate thread. Below is the link. All i have added is team name. How do i get the sum or a count by team wise.

https://ask.sqlservercentral.com/questions/119477/how-to-count-and-sum-values-of-multiple-column-tea.html

more ▼

answered Apr 06, 2015 at 12:03 PM

avatar image

nara
41 1 1 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x53
x48
x6
x5
x3

asked: Mar 14, 2015 at 02:24 PM

Seen: 6379 times

Last Updated: Apr 08, 2015 at 05:12 AM

Copyright 2018 Redgate Software. Privacy Policy