# question

## How to count and sum values of multiple column

![Input table![Result looking for][1] [1]: /storage/temp/2236-output.jpg
input.jpg (79.0 KiB)
output.jpg (35.7 KiB)
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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

·
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)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
This is more elegant than what I came up with. ;-) PIVOT/UNPIVOT is definitely NOT one of my stronger skills. ;-)
0 Likes 0 ·
·
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?
0 Likes 0 ·
·
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...
0 Likes 0 ·
·
Can we get the distinct values? and then sum. instead of specifying as Verified, Added, Changed.
0 Likes 0 ·
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]

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
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.
0 Likes 0 ·
·
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]
0 Likes 0 ·

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

![New Input File][1] ![New Output Result][2] [1]: /storage/temp/2327-input1.jpg [2]: /storage/temp/2328-output1.jpg This is a question by the way.. How to count and sum values of multiple column - Team wise

input1.jpg (31.1 KiB)
output1.jpg (42.6 KiB)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can anyone assist me on this?

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.