Pivot columns with aggregated function and calculate total of aggregated value in last column

Question

Hi my source data is like,

date    E1  E2  E3  Total
05-25-15    4   2   2   8
05-26-15    5   0   4   9
05-27-15    5   0   4   9
05-28-15    0   4   8   12
05-29-15    0   5   2   7
05-30-15    0   8   4   12

and i want result like,

Error type  Total   Cumulative  Contribution%
E1          190         190       190/490
E2          100         290       290/490
E3          80          370       370/490
E4          70          440       440/490

and result something like,

Error type 05-25-15 05-26-15 Total Contribution% E1 4 5 9 9/17 E2 2 0 2 2/17 E3 2 4 6 6/17

hey Tim ,i am really new in sql , I want result like error 1,2,3 columns as in row and the total sum column in second column. like in second table. — Jack, Jul 15 '15 at 10:08
the number of column is 46 error type, the result like in col1 all 46 error and second col2 total sum of each error in front of them. — Jack, Jul 15 '15 at 10:13

score 0 · Accepted Answer · answered Jul 15 '15 at 10:39

Try this query

select a.error 'Error Type',a.Total,sum(b.Total) 'Cumulative',convert(varchar(25),a.Total)+'/'+convert(varchar(25),sum(a.total) over()) from(
select 1 as id, 'E1' as Error ,sum(E1) as total
from tablename
Union all
select 2 as id, 'E2' as Error ,sum(E2) as total
from tablename
Union all
select 3 as id, 'E3' as Error ,sum(E3) as total
from tablename
Union all
select 4 as id, 'E4' as Error ,sum(E4) as total
from tablename)a
join (select 1 as id, 'E1' as Error ,sum(E1) as total
from tablename
Union all
select 2 as id, 'E2' as Error ,sum(E2) as total
from tablename
Union all
select 3 as id, 'E3' as Error ,sum(E3) as total
from tablename
Union all
select 4 as id, 'E4' as Error ,sum(E4) as total
from tablename)b on b.id<=a.id

great !!! Thanks Mukesh, it helps me to solved my problem. – Jack Jul 15 '15 at 12:23 — Jack, Jul 15 '15 at 12:23
How i calculate sum of Total in last row – Jack Jul 16 '15 at 06:30 — Jack, Jul 16 '15 at 06:30
It is add manual in ui. – Mukesh Kalgude Jul 16 '15 at 07:11 — Mukesh Kalgude, Jul 16 '15 at 07:11

James Z · Answer 2 · 2015-07-16T10:22:08.597

0

If you want an alternative way, this is how you can do this using unpivot, CTEs and rollup:

;with CTE as (SELECT ErrorType, sum(Value) as Value
FROM (SELECT E1, E2, E3 FROM table1) p
UNPIVOT (Value FOR ErrorType IN (E1, E2, E3)
)AS u group by ErrorType)

select ErrorType, 
       Value as Total, sum(Value) over(order by ErrorType asc) As Cumulative,
       convert(varchar, sum(Value) over(order by ErrorType asc)) 
           + '/' + convert(varchar, sum(Value) over()) As Contribution
from CTE

The first CTE transforms the columns into rows + sums them, the second one calculates the values for the rows and the final select is there just for the rollup (=total) row.

You can test this in SQL Fiddle.

Edit: Removed the total row completely

edited Jul 16 '15 at 10:22

answered Jul 15 '15 at 19:00

James Z

12,209
10
24
44

@DatabasePirate This works just fine with zeros and even NULLs, as long as you have at least one non-null value, see: http://sqlfiddle.com/#!3/771b5/1 But why would you store NULLs to a count -column anyway, that's just asking for trouble – James Z Jul 16 '15 at 07:39
The last row Total that i don't want in first row, bcs i am sorting Total by DESC order then it comes first. or how i remove this column from my result ,i will manage it in UI – Jack Jul 16 '15 at 09:05
@DatabasePirate Without the total row the SQL is lot more simpler and you can remove the second CTE completely. I edited the answer + SQL Fiddle to have a version without total row. – James Z Jul 16 '15 at 10:22
@DatabasePirate If you want to sort by total, this is how it should work: http://sqlfiddle.com/#!3/71464/2 – James Z Jul 16 '15 at 10:28
Yess !!! This is what i am looking for, Now everything is perfect. Thanks James. – Jack Jul 16 '15 at 11:36
Hey James, how to add all date as column name in the same result before total i want 05-25-15,05-26-15.... date columns and their total by date. Now we take all the dates total. – Jack Jul 17 '15 at 04:06
@DatabasePirate Assuming the number of dates / columns isn't fixed, it gets quite messy and you'll have to use dynamic sql to create a pivot: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – James Z Jul 17 '15 at 05:28
how can i add date as column name in our case. I am not able to change our query. i think i m missing something. can you help me by changing our answered query or on SQL Fiddle. – Jack Jul 17 '15 at 12:10

Pivot columns with aggregated function and calculate total of aggregated value in last column

2 Answers2