## Forum Stats

• 3,757,567 Users
• 2,251,246 Discussions

Discussions

# Matrix

A B C D E F G total1
AA 1 2 3 4 5 6 7 28
BB 2 2 3 4 5 6 7 29
CC 3 4 5 6 7 8 7 40
DD 4 3 4 5 6 7 7 36
total2 10 11 15 19 23 27 28 133
How do I write a sql (oracle and t-sql) which would help me sum up the rows and columns . Thanks
«1

• Is this your sample input or output? It looks like they're summed up already.

Please post proper create table and insert statements.
• Thats a sample and I need total1 and total2 columns
Thanks
• With model caluse (10g)
```SELECT  a,b,c,d,e,f,g,h,total1
model
dimension by (a)
measures(  b,c,d,e,f,g,h,NVL (b, 0)
+ NVL (c, 0)
+ NVL (d, 0)
+ NVL (e, 0)
+ NVL (f, 0)
+ NVL (g, 0)
+ NVL (h, 0) total1)
(b['Total2']=sum(b)[any],
c['Total2']=sum(c)[any],
d['Total2']=sum(d)[any],
e['Total2']=sum(e)[any],
f['Total2']=sum(f)[any],
g['Total2']=sum(g)[any],
h['Total2']=sum(h)[any],
total1['Total2']=sum(Total1)[any])```
Demo
```set linesize 200
drop table t;
create table t ( a varchar2(10),  b number,  c number , d number, e number,  f number,  g number, h number);

insert into t
SELECT 'AA' a, 1 b, 2 c, 3 d, 4 e, 5 f, 6 g, 7 h
FROM DUAL
UNION ALL
SELECT 'BB', 2, 2, 3, 4, 5, 6, 7
FROM DUAL
UNION ALL
SELECT 'CC', 3, 4, 5, 6, 7, 8, 7
FROM DUAL
UNION ALL
SELECT 'DD', 4, 3, 4, 5, 6, 7, 7
FROM DUAL;

SELECT  a,b,c,d,e,f,g,h,total1
FROM t
model
dimension by (a)
measures(  b,c,d,e,f,g,h,NVL (b, 0)
+ NVL (c, 0)
+ NVL (d, 0)
+ NVL (e, 0)
+ NVL (f, 0)
+ NVL (g, 0)
+ NVL (h, 0) total1)
(b['Total2']=sum(b)[any],
c['Total2']=sum(c)[any],
d['Total2']=sum(d)[any],
e['Total2']=sum(e)[any],
f['Total2']=sum(f)[any],
g['Total2']=sum(g)[any],
h['Total2']=sum(h)[any],
Total1['Total2']=sum(Total1)[any]);

Table dropped.
Table created.
4 rows created.

A                   B          C          D          E          F          G          H     TOTAL1
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
AA                  1          2          3          4          5          6          7         28
BB                  2          2          3          4          5          6          7         29
CC                  3          4          5          6          7          8          7         40
DD                  4          3          4          5          6          7          7         36
Total2             10         11         15         19         23         27         28        133

5 rows selected.```
Edited by: Salim Chelabi on 2009-05-08 11:21
• Solution with GROUP BY ROLLUP, without model clause .

Regards Salim.

Regards Salim.
```/* Formatted on 2009/05/08 14:41 (Formatter Plus v4.8.8) */
SELECT   NVL (a, 'Total2') a, NVL (b, SUM (b)) b, NVL (c, SUM (c)) c,
NVL (d, SUM (d)) d, NVL (e, SUM (e)) e, NVL (f, SUM (f)) f,
NVL (g, SUM (g)) g, NVL (h, SUM (h)) h,
NVL (  NVL (b, 0)
+ NVL (c, 0)
+ NVL (d, 0)
+ NVL (e, 0)
+ NVL (f, 0)
+ NVL (g, 0)
+ NVL (h, 0),
SUM (  NVL (b, 0)
+ NVL (c, 0)
+ NVL (d, 0)
+ NVL (e, 0)
+ NVL (f, 0)
+ NVL (g, 0)
+ NVL (h, 0)
)
) total1
FROM t
GROUP BY ROLLUP ((a,
b,
c,
d,
e,
f,
g,
h,
NVL (b, 0)
+ NVL (c, 0)
+ NVL (d, 0)
+ NVL (e, 0)
+ NVL (f, 0)
+ NVL (g, 0)
+ NVL (h, 0)
));

A                                         B          C          D          E          F          G          H     TOTAL1
-------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
AA                                        1          2          3          4          5          6          7         28
BB                                        2          2          3          4          5          6          7         29
CC                                        3          4          5          6          7          8          7         40
DD                                        4          3          4          5          6          7          7         36
Total2                                   10         11         15         19         23         27         28        133

5 rows selected.```
Edited by: Salim Chelabi on 2009-05-08 11:44
• You can find the sql how to get the, if you search on web or this forums.

To write a sql which runs on both Oracle and Sql Server you will have to write a ANSI SQL 92 compliant sql.

ANSI GROUP BY CUBE ROLLUP syntax by is supported SQL Server 2008 onwards.

SS
• Or (assuming no null values - otherwise wrap the columns with NVL):
```select   t.*, b + c + d + e + f + g + h total1 from t
union all
select   'total2',
sum (b),
sum (c),
sum (d),
sum (e),
sum (f),
sum (g),
sum (h),
sum (b + c + d + e + f + g + h) total1
from   t

A               B          C          D          E          F          G          H     TOTAL1
------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
AA              1          2          3          4          5          6          7         28
BB              2          2          3          4          5          6          7         29
CC              3          4          5          6          7          8          7         40
DD              4          3          4          5          6          7          7         36
total2         10         11         15         19         23         27         28        133

5 rows selected.```
• Yesterday, I learned nice rollup usage from below thread :-)
899486

Although I will research,
I want to know whether this rollup usage is documented or not.
```col b for 99
col c for 99
col d for 99
col e for 99
col f for 99
col g for 99
col h for 99

with T as (
SELECT 'AA' a,1 b, 2 c,3 d,4 e,5 f,6 g,7 h FROM DUAL UNION
SELECT 'BB',2,2,3,4,5,6,7 FROM DUAL UNION
SELECT 'CC',3,4,5,6,7,8,7 FROM DUAL UNION
SELECT 'DD',4,3,4,5,6,7,7 FROM DUAL)
select decode(grouping(a),0,a,'Total2') as a,
sum(b) as b,sum(c) as c,sum(d) as d,sum(e) as e,
sum(f) as f,sum(g) as g,sum(h) as h,
sum(b+c+d+e+f+g+h) as TOTAL1
from t
group by rollup((a,b,c,d,e,f,g,h));

A        B   C   D   E   F   G   H  TOTAL1
------  --  --  --  --  --  --  --  ------
AA       1   2   3   4   5   6   7      28
BB       2   2   3   4   5   6   7      29
CC       3   4   5   6   7   8   7      40
DD       4   3   4   5   6   7   7      36
Total2  10  11  15  19  23  27  28     133```
• Hi Aketi Jyuuzou ,

Sincerely I learned by Frank. i don't know if it's documented or not.

Thanks Frank.

You must put SUM (nvl(b,0) + nvl(c,0) + nvl(d,0) + nvl(e ,0)+nvl( f,0) + nvl(g,0)+ nvl(h,0)) if one of all columns can be null.
```WITH t AS
(SELECT 'AA' a, 1 b, 2 c, 3 d, 4 e, 5 f, 6 g, 7 h
FROM DUAL
UNION
SELECT 'BB', NULL, 2, 3, 4, 5, 6, 7
FROM DUAL
UNION
SELECT 'CC', 3, NULL, 5, 6, 7, 8, 7
FROM DUAL
UNION
SELECT 'DD', 4, 3, 4, NULL, 6, 7, 7
FROM DUAL)
SELECT   DECODE (GROUPING (a), 0, a, 'Total2') AS a, SUM (b) AS b,
SUM (c) AS c, SUM (d) AS d, SUM (e) AS e, SUM (f) AS f, SUM (g) AS g,
SUM (h) AS h, SUM (b + c + d + e + f + g + h) AS total1
FROM t
GROUP BY ROLLUP ((a, b, c, d, e, f, g, h));

A        B   C   D   E   F   G   H     TOTAL1
------ --- --- --- --- --- --- --- ----------
AA       1   2   3   4   5   6   7         28
BB           2   3   4   5   6   7
CC       3       5   6   7   8   7
DD       4   3   4       6   7   7
Total2   8   7  15  14  23  27  28         28

5 rows selected.```
• Today I researched Oracle ducuments.
Then I found that this rollup usage is documented B-)

http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19217-02/aggreg.htm#sthref1653