cube ve rollup ile Toplam hesaplamak

 

DECLARE @test TABLE

(

      ID    int identity(1,1),

      [Type] nvarchar(100),

      Name nvarchar(100)

)

 

INSERT INTO @test VALUES( ‘normal’,‘Film’ )

INSERT INTO @test VALUES( ‘normal’,‘Film’ )

INSERT INTO @test VALUES( ‘Korku’,‘Film’ )

INSERT INTO @test VALUES( ‘Savaş’,‘Film’ )

INSERT INTO @test VALUES( ‘Savaş’,‘Film’ )

INSERT INTO @test VALUES( ‘Savaş’,‘Film’ )

INSERT INTO @test VALUES( ‘Savaş’,‘Film’ )

INSERT INTO @test VALUES( ‘Komedi’,‘Film’ )

INSERT INTO @test VALUES( ‘Komedi’,‘Film’ )

INSERT INTO @test VALUES( ‘Komedi’,‘Film’ )

INSERT INTO @test VALUES( ‘Pop’,‘Müzik’ )

INSERT INTO @test VALUES( ‘Pop’,‘Müzik’ )

INSERT INTO @test VALUES( ‘Rock’,‘Müzik’ )

INSERT INTO @test VALUES( ‘Hard Rock’,‘Müzik’ )

INSERT INTO @test VALUES( ‘Hard Rock’,‘Müzik’ )

INSERT INTO @test VALUES( ‘Hard Rock’,‘Müzik’ )

INSERT INTO @test VALUES( ‘Metal’,‘Müzik’ )

INSERT INTO @test VALUES( ‘Metal’,‘Müzik’ )

 

 

 

 

SELECT COUNT(ID) as Count, Name FROM @test GROUP BY Name

 

Count Name

10    Film

8     Müzik

 

SELECT COUNT(ID), COALESCE(Name,‘Toplam’) FROM @test GROUP BY Name with cube

Count Name

10    Film

8     Müzik

18    Toplam

 

 

SELECT COUNT(ID) as Count,Type, Name FROM @test GROUP BY [Type],Name with cube

Count    Type      Name

3              Komedi                Film

1              Korku    Film

2              normal  Film

4              Savas     Film

10           NULL     Film

3              Hard Rock            Müzik

2              Metal    Müzik

2              Pop        Müzik

1              Rock      Müzik

8              NULL     Müzik

18           NULL     NULL

3              Hard Rock            NULL

3              Komedi                NULL

1              Korku    NULL

2              Metal    NULL

2              normal  NULL

2              Pop        NULL

1              Rock      NULL

4              Savas     NULL

 

SELECT COUNT(ID) as Count, Name FROM @test GROUP BY Name with rollup

Count    Name

10           Film

8              Müzik

18           NULL

 

SELECT COUNT(ID) as Count,Type, Name FROM @test GROUP BY [Type],Name with rollup

 

Count    Type      Name

3              Hard Rock            Müzik

3              Hard Rock            NULL

3              Komedi                Film

3              Komedi                NULL

1              Korku    Film

1              Korku    NULL

2              Metal    Müzik

2              Metal    NULL

2              normal  Film

2              normal  NULL

2              Pop        Müzik

2              Pop        NULL

1              Rock      Müzik

1              Rock      NULL

4              Savas     Film

4              Savas     NULL

18           NULL     NULL

 

SELECT COUNT(ID) as Count,[Type], Name FROM @test GROUP BY rollup([Type],Name)

SELECT COUNT(ID) as Count,[Type], Name FROM @test GROUP BY cube([Type],Name)