asked on

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!

Aaron Tomosky

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

Mark Wills

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

Mark Wills

OOps, spotted a mistake, not grouping for the overall.

Back in a minute...

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Adrian Cross

ASKER

Superb answer Mark Wills.
Thanks

Mark Wills

A pleasure Adrian :)