SQL Pivot Table - SUM in two columns

HI,
I'd like to display the table in the picdatadata 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!
Adrian CrossAsked:
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.

Aaron TomoskySD-WAN SimplifiedCommented:
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;

Open in new window

0
Mark WillsTopic AdvisorCommented:
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

Open in new window

0
Mark WillsTopic AdvisorCommented:
OOps, spotted a mistake, not grouping for the overall.

Back in a minute...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark WillsTopic AdvisorCommented:
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 

Open in new window

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

Open in new window

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
2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Adrian CrossAuthor Commented:
Superb answer Mark Wills.
Thanks
0
Mark WillsTopic AdvisorCommented:
A pleasure Adrian :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Pivot Tables

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.