ROLLUP OR CUBE?

ROLLUP OR CUBE?

Post by alexandr » Thu, 12 Jul 2001 03:45:06



I've got a table as follow:

CREATE TABLE MyTable
(
  field1 int identity(1,1),
  field2 int,
  field3 int,
  CONSTRAINT PK_MyTable PRIMARY KEY (field1)
)

Now, imagine that I have the following values in myn table:
field1  field2  field3
1       10       30
2       20       30
3       50       30
4       70       100

I'd like to obtain a resultset as follow:
field1   field2   field3
 1       10       30
 2       20       30
 3       50       30
 4       70       30
 null    150      120

Notice that the last row of my resultset is the sum of the
column. How can I do that without using a COMPUTE or
COMPUTE BY statement??

 
 
 

ROLLUP OR CUBE?

Post by Hirantha S Hettiarachch » Thu, 12 Jul 2001 04:22:23


Alexandre
try
CREATE TABLE MyTable
(
  field1 int identity(1,1),
  field2 int,
  field3 int,
  CONSTRAINT PK_MyTable PRIMARY KEY (field1)
)

Go
Insert into MyTable(field2,field3) values(20,30)
Insert into MyTable(field2,field3) values(40,50)
Insert into MyTable(field2,field3) values(50,50)
Insert into MyTable(field2,field3) values(70,50)
Insert into MyTable(field2,field3) values(10,60)
Insert into MyTable(field2,field3) values(20,70)
Insert into MyTable(field2,field3) values(20,70)
Go
Select *, case when field1 is Null then 2 else 1 end as orderby
from ( Select field1,field2,field3 from MyTable
Union
Select Null as field1,Sum(field2),Sum(field3) from MyTable)as a
Order by 4

May be someone will have a clever Idea.. so that it wont have an additional
column
--
hth
~~~~~~~~~~~~~~~~~~~~~~~~~~
Hirantha S Hettiarachchi
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> I've got a table as follow:

> CREATE TABLE MyTable
> (
>   field1 int identity(1,1),
>   field2 int,
>   field3 int,
>   CONSTRAINT PK_MyTable PRIMARY KEY (field1)
> )

> Now, imagine that I have the following values in myn table:
> field1  field2  field3
> 1       10       30
> 2       20       30
> 3       50       30
> 4       70       100

> I'd like to obtain a resultset as follow:
> field1   field2   field3
>  1       10       30
>  2       20       30
>  3       50       30
>  4       70       30
>  null    150      120

> Notice that the last row of my resultset is the sum of the
> column. How can I do that without using a COMPUTE or
> COMPUTE BY statement??


 
 
 

ROLLUP OR CUBE?

Post by Steve Dassi » Thu, 12 Jul 2001 06:22:52


Hi,

With thanks to Hirantha for ddl.

Using olap in group by is a pita for examples like this.

select field1,sum(field2) as field2,sum(field3) as  field3
from #mytable
group by field1,field2,field3 with rollup
having (grouping(field1)=1 and grouping(field2)=1 and grouping(field3)=1)
or (grouping(field1)=0 and grouping(field2)=0 and grouping(field3)=0)
order by grouping(field1),field1

You can do the same thing with Rac s2k version utility a tad easier.


              f2 as field2,f3 as field3 from rac order by rd '

field1         field2          field3
----------- ----------- -----------
1                20             30
2                40             50
3                50             50
4                70             50
5                10             60
6                20             70
7                20             70
NULL        230           380

stevie
www.angelfire.com/ny4/rac/
Rac s2k version available soon.

 
 
 

ROLLUP OR CUBE?

Post by Steve Kas » Thu, 12 Jul 2001 06:34:10


Select field1,field2,field3 from #MyTable
Union all
Select Null as field1,Sum(field2),Sum(field3) from #MyTable

?

Steve


> Alexandre
> try
> CREATE TABLE MyTable
> (
>   field1 int identity(1,1),
>   field2 int,
>   field3 int,
>   CONSTRAINT PK_MyTable PRIMARY KEY (field1)
> )

> Go
> Insert into MyTable(field2,field3) values(20,30)
> Insert into MyTable(field2,field3) values(40,50)
> Insert into MyTable(field2,field3) values(50,50)
> Insert into MyTable(field2,field3) values(70,50)
> Insert into MyTable(field2,field3) values(10,60)
> Insert into MyTable(field2,field3) values(20,70)
> Insert into MyTable(field2,field3) values(20,70)
> Go
> Select *, case when field1 is Null then 2 else 1 end as orderby
> from ( Select field1,field2,field3 from MyTable
> Union
> Select Null as field1,Sum(field2),Sum(field3) from MyTable)as a
> Order by 4

> May be someone will have a clever Idea.. so that it wont have an additional
> column
> --
> hth
> ~~~~~~~~~~~~~~~~~~~~~~~~~~
> Hirantha S Hettiarachchi
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > I've got a table as follow:

> > CREATE TABLE MyTable
> > (
> >   field1 int identity(1,1),
> >   field2 int,
> >   field3 int,
> >   CONSTRAINT PK_MyTable PRIMARY KEY (field1)
> > )

> > Now, imagine that I have the following values in myn table:
> > field1  field2  field3
> > 1       10       30
> > 2       20       30
> > 3       50       30
> > 4       70       100

> > I'd like to obtain a resultset as follow:
> > field1   field2   field3
> >  1       10       30
> >  2       20       30
> >  3       50       30
> >  4       70       30
> >  null    150      120

> > Notice that the last row of my resultset is the sum of the
> > column. How can I do that without using a COMPUTE or
> > COMPUTE BY statement??

 
 
 

ROLLUP OR CUBE?

Post by Hirantha S Hettiarachch » Thu, 12 Jul 2001 19:31:03


Uh thats what I have missed :-) Union ALL :-) thanks Steve

--
hth
~~~~~~~~~~~~~~~~~~~~~~~~~~
Hirantha S Hettiarachchi
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Select field1,field2,field3 from #MyTable
> Union all
> Select Null as field1,Sum(field2),Sum(field3) from #MyTable

> ?

> Steve


> > Alexandre
> > try
> > CREATE TABLE MyTable
> > (
> >   field1 int identity(1,1),
> >   field2 int,
> >   field3 int,
> >   CONSTRAINT PK_MyTable PRIMARY KEY (field1)
> > )

> > Go
> > Insert into MyTable(field2,field3) values(20,30)
> > Insert into MyTable(field2,field3) values(40,50)
> > Insert into MyTable(field2,field3) values(50,50)
> > Insert into MyTable(field2,field3) values(70,50)
> > Insert into MyTable(field2,field3) values(10,60)
> > Insert into MyTable(field2,field3) values(20,70)
> > Insert into MyTable(field2,field3) values(20,70)
> > Go
> > Select *, case when field1 is Null then 2 else 1 end as orderby
> > from ( Select field1,field2,field3 from MyTable
> > Union
> > Select Null as field1,Sum(field2),Sum(field3) from MyTable)as a
> > Order by 4

> > May be someone will have a clever Idea.. so that it wont have an
additional
> > column
> > --
> > hth
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~
> > Hirantha S Hettiarachchi
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > I've got a table as follow:

> > > CREATE TABLE MyTable
> > > (
> > >   field1 int identity(1,1),
> > >   field2 int,
> > >   field3 int,
> > >   CONSTRAINT PK_MyTable PRIMARY KEY (field1)
> > > )

> > > Now, imagine that I have the following values in myn table:
> > > field1  field2  field3
> > > 1       10       30
> > > 2       20       30
> > > 3       50       30
> > > 4       70       100

> > > I'd like to obtain a resultset as follow:
> > > field1   field2   field3
> > >  1       10       30
> > >  2       20       30
> > >  3       50       30
> > >  4       70       30
> > >  null    150      120

> > > Notice that the last row of my resultset is the sum of the
> > > column. How can I do that without using a COMPUTE or
> > > COMPUTE BY statement??

 
 
 

ROLLUP OR CUBE?

Post by Darren Brinksneade » Thu, 12 Jul 2001 23:01:52


SELECT field1, SUM(field2) AS Field2, SUM(field3) AS Field3 FROM MyTable
GROUP BY field1
WITH ROLLUP

Result:
field1          Field2      Field3
----------- ----------- -----------
1               10          30
2               20          30
3               50          30
4               70          30
NULL      150         120

(5 row(s) affected)

--
Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT


Quote:> I've got a table as follow:

> CREATE TABLE MyTable
> (
>   field1 int identity(1,1),
>   field2 int,
>   field3 int,
>   CONSTRAINT PK_MyTable PRIMARY KEY (field1)
> )

> Now, imagine that I have the following values in myn table:
> field1  field2  field3
> 1       10       30
> 2       20       30
> 3       50       30
> 4       70       100

> I'd like to obtain a resultset as follow:
> field1   field2   field3
>  1       10       30
>  2       20       30
>  3       50       30
>  4       70       30
>  null    150      120

> Notice that the last row of my resultset is the sum of the
> column. How can I do that without using a COMPUTE or
> COMPUTE BY statement??

 
 
 

ROLLUP OR CUBE?

Post by Steve Dassi » Fri, 13 Jul 2001 01:36:53



Quote:> SELECT field1, SUM(field2) AS Field2, SUM(field3) AS Field3 FROM MyTable
> GROUP BY field1
> WITH ROLLUP

 Very good.
You finally got one:).

stevie

 
 
 

ROLLUP OR CUBE?

Post by Darren Brinksneade » Fri, 13 Jul 2001 01:49:04


Finally? That Hurts! ;-)

--
Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT




> > SELECT field1, SUM(field2) AS Field2, SUM(field3) AS Field3 FROM MyTable
> > GROUP BY field1
> > WITH ROLLUP
>  Very good.
> You finally got one:).

> stevie

 
 
 

ROLLUP OR CUBE?

Post by Steve Dassi » Fri, 13 Jul 2001 02:27:12



Quote:> Finally? That Hurts! ;-)

Only kidding, chill:)

stevie

 
 
 

1. Failed to update custom rollup for cube levels

Hi,

We get the following error message when processing some of the cubes
(not all of the cubes):
Error(-2147221421): Internal error (Failed to update custom rollup for
cube levels);
Error(-2147221421): Internal error (Unexpected internal error (Cube
'License_Snapshots'));

The cube can not be opened by the Analysis Services Enterprise. The
error message is : Internal Error. Unable to open the cube. Try to
restart the application.

I restarted the Olap Service on the server but it did not help.

Thanks for your help,
Shirly.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. CANNOT BACKUP A LOCAL DATABASE TO A REMOTE SERVER

3. Error:Failed to update custom rollup for cube levels

4. help with runtime error

5. ROLLUP and CUBE and COMPUTE Oh, my!

6. INGRES on Suns and Goulds?

7. group by vs rollup vs cube

8. Is there a NUM function?

9. cant make rollup or cube look like compute

10. Using RollUp or Cube in Oracle 8i Query or Similar like in Sql Server ?

11. Rollup of a Cube with lower level calcs

12. Custom rollup formulas and virtual cubes

13. custom rollup but in 1 cube only ?