# SQL Pivot Table - SUM in two columns

HI,
I'd like to display the table in the pic in a pivot view (grouped by mygroup) with 2 total columns, Marks & Extra.

The result I'm after it's this...

1        2          3             4          5          6     TOTAL MARKS      TOTAL EXTRA
1.5     0          1.0          0           0.5    1.5              4.5                       2
3 .0   1          0.5           0.5      0.5      0.5              6.0                       3

Many Thanks!
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Director of Solutions ConsultingCommented:
try this
``````SELECT mygroup AS TotalMarksByMyGroup,[1],[2],[3],[4],[5],[6]
FROM
(SELECT mygroup, q, marks
FROM tablename) AS SourceTable
PIVOT
(
SUM(marks)
FOR q IN ([1],[2],[3],[4],[5],[6])
) AS PivotTable;
``````
Need to accumulate the totals, but at different places....
``````SELECT [1],[2],[3],[4],[5],[6],isnull([1],0)+isnull([2],0)+isnull([3],0)+isnull([4],0)+isnull([5],0)+isnull([6],0) as [TOTAL MARKS], [TOTAL EXTRA]
FROM
(SELECT mygroup, q, marks, sum(extra) as [TOTAL EXTRA]
FROM yourtable
GROUP BY mygroup,q,marks) src
PIVOT
(
SUM(marks)  FOR q IN ([1],[2],[3],[4],[5],[6]) ) pvt
``````
OOps, spotted a mistake, not grouping for the overall.

Back in a minute...
Sorry about that, no need to group in two places, but we do need to group overall

Made the rooky mistake that pivot means aggregation but it doesnt mean that at all for the outer select. So, we need to take care of that and when we have columns not included in the pivot part, then they are auto included.

So, always best to take control of the outer query aggregation to avoid any surprises
``````SELECT sum(isnull([1],0)) as [1],sum(isnull([2],0)) as [2],sum(isnull([3],0)) as [3],sum(isnull([4],0)) as [4],sum(isnull([5],0)) as [5],sum(isnull([6],0)) as [6]
,sum(isnull([1],0))+sum(isnull([2],0))+sum(isnull([3],0))+sum(isnull([4],0))+sum(isnull([5],0))+sum(isnull([6],0)) as [TOTAL MARKS]
,(select sum(isnull(extra,0)) from yourtable x where x.mygroup = pvt.mygroup) as [TOTAL EXTRA]
FROM
(SELECT mygroup, q, marks
FROM yourtable) src
PIVOT
( SUM(marks)  FOR q IN ([1],[2],[3],[4],[5],[6]) ) pvt
group by mygroup
``````
And thats the "rookie" mistake that is all too easy to do (as witnessed by my own mistake). Case in point... While we can write
``````SELECT [1],[2],[3],[4],[5],[6]
,isnull([1],0)+isnull([2],0)+isnull([3],0)+isnull([4],0)+isnull([5],0)+isnull([6],0) as [TOTAL MARKS]
,(select sum(isnull(extra,0)) from yourtable x where x.mygroup = pvt.mygroup) as [TOTAL EXTRA]
FROM
(SELECT mygroup, q, marks
FROM yourtable) src
PIVOT
( SUM(marks)  FOR q IN ([1],[2],[3],[4],[5],[6]) ) pvt
``````
See what happens if you add a column to the src subquery. Try it with "SELECT mygroup, q, marks, extra FROM yourtable"

Thats when you need to take control over the whole pivot function.

It is a bit like SELECT * which we do for convenience sometimes - knowing we should really spell out the columns :)

Wrote an Article about it : https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html

Experts Exchange Solution brought to you by