[belajar-excel] GROUP BY WITH ROLLUP

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[belajar-excel] GROUP BY WITH ROLLUP

Milis Belajar Excel mailing list
Dear Be-Excelers,
 

 Selain menggunakan pivot table, saya menggunakan SQL dengan Microsoft Query di Excel untuk merangkum tabel dari beberapa file.
 Kira-kira contohnya seperti di ini:
 '=====================
 SELECT [COMPANY],SUM([AMOUNT]) AS [TOTAL AMOUNT]
 FROM
 (
 

 

 SELECT
  UCASE(mat.[COMPANY NAME]) AS [COMPANY],
  mat.[RFM_ID],
  mat.[DESCRIPTION],
  mat.[AMOUNT]
 FROM [Y:\LOKASI FILE\FILEPERTAMA.xlsx].[MTRL$] mat
 

 UNION ALL
 

 SELECT
  UCASE(work.[COMPANY NAME]) AS [COMPANY],
  work.[WO_ID],
  work.[DESCRIPTION],
  work.[AMOUNT]
 FROM [Y:\LOKASIFILE\FILEKEDUA.xlsx].[WORK$] work
 

 )
 GROUP BY [COMPANY]
 '===========================
 Untuk menampilkan [Grand Total], saya biasanya menggunakan bantuan pivot table, tetapi bagaimanakah caranya untuk menampilkan grandtotal dengan GROUP BY WITH ROLLUP di SQL? ataukah ada cara lainnya ?
 

 Terimakasih,
 -zainul
 

 

 

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] GROUP BY WITH ROLLUP

Milis Belajar Excel mailing list
mas Zainul,

opsi RollUp adanya di ms sql server.
koneksi data Excel to Excel menggunakan ACE OLE DB yang menggunakan sql
standar.

1. Biasanya begini :
Select  kolom_key_tipe_text , Sum( kolom_yg_diSum )
From tabelnya
Group By kolom_key_tipe_text
UNION ALL
Select 'Grand Total', Sum( kolom_yg_diSum )
From tabelnya

2. Kalau keynya ada 2 kolom :
Select  kolom_key1_tipe_text , kolom_key2_tipe_text , Sum( kolom_yg_diSum )
From tabelnya
Group By kolom_key1_tipe_text, kolom_key2_tipe_text
UNION ALL
Select 'Grand Total' , 'Grand Total', Sum( kolom_yg_diSum )
From tabelnya

3. Kalau ada subtotal per key2 nya juga, jadi perlu ditambahin ini :
UNION ALL
Select 'SubTotal' , kolom_key2_tipe_text, Sum( kolom_yg_diSum )
From tabelnya
Group By kolom_key2_tipe_text


Contoh data di dalam tabel bernama 'tabelnya'
kolom_key1_tipe_text         kolom_key2_tipe_text           kolom_yg_diSum
   A
X                                      10
   A
X                                      20
   A                                                 Y
                                    400
   B                                                 Y
                                   5000

Query 0 :
Select  kolom_key1_tipe_text as Nama, kolom_key2_tipe_text as Kota ,
kolom_yg_diSum as NilaiTotal
From tabelnya

hasilnya : (sama dengan tabel data bernama 'tabelnya') dengan nama kolom
yang ditentukan sendiri
Nama                                         Kota
   NilaiTotal
   A
X                                      10
   A
X                                      20
   A                                                 Y
                                    400
   B                                                 Y
                                   5000


Query 1 :
Select  kolom_key1_tipe_text as Nama, kolom_key2_tipe_text as Kota , Sum(
kolom_yg_diSum ) as NilaiTotal
From tabelnya
Group By kolom_key1_tipe_text, kolom_key2_tipe_text

hasilnya : di grup berdasar kolom ke-1 dan ke-2   (2 key) : total setiap
nama di setiap kota
Nama             Kota                              NilaiTotal
   A                    X                                        30
   A                    Y                                      400
   B                    Y                                    5000

Query 2 :
Select  kolom_key1_tipe_text as Nama, 'Subtotal Key1' as Kota , Sum(
kolom_yg_diSum ) as NilaiTotal
From tabelnya
Group By kolom_key1_tipe_text

hasilnya : subtotal setiap Nama
Nama             Kota                              NilaiTotal
   A                Subtotal Key1                     430
   B                Subtotal Key1                   5000

Query 3 :
Select  'Subtotal Key2' as Nama, kolom_key2_tipe_text as Kota , Sum(
kolom_yg_diSum ) as NilaiTotal
From tabelnya
Group By kolom_key2_tipe_text

hasilnya : subtotal setiap kota
Nama                          Kota                            NilaiTotal
   Subtotal Key2           X                                       30
   Subtotal Key2           Y                                   5400

Query 4 :
Select 'Grand Total' as Nama , 'Grand Total' as Kota, Sum( kolom_yg_diSum )
as NilaiTotal
From tabelnya

Hasilnya : total
Nama                          Kota                            NilaiTotal
      Grand Total             Grand Total                    5430


Trus masing-masing query yang kolom-kolom select-nya sama makna di gabung
utuh (UNION ALL)
                 Query 0
*Union All* Query 1
*Union All* Query 2
*Union All* Query 3
*Union All* Query 4

Hasilnya : ya kaya gini
Nama                          Kota                            NilaiTotal
   A
X                                       10
   A
X                                       20
   A                                                 Y
                                    400
   B                                                 Y
                                  5000
   A
X                                        30
   A
Y                                      400
   B
Y                                    5000
   A                                             Subtotal Key1
        430
   B                                             Subtotal
Key1                   5000
   Subtotal Key2                             X
                            30
   Subtotal Key2                             Y
      5400
      Grand Total                             Grand Total
                 5430

Sila dipilih dan diatur sesuai kebutuhan

Regards,
Kid


2017-07-28 7:22 GMT+07:00 [hidden email] [belajar-excel] <
[hidden email]>:

>
>
> Dear Be-Excelers,
>
>
> Selain menggunakan pivot table, saya menggunakan SQL dengan Microsoft
> Query di Excel untuk merangkum tabel dari beberapa file.
>
> Kira-kira contohnya seperti di ini:
>
> '=====================
>
> SELECT [COMPANY],SUM([AMOUNT]) AS [TOTAL AMOUNT]
>
> FROM
>
> (
>
>
>
> SELECT
>
>  UCASE(mat.[COMPANY NAME]) AS [COMPANY],
>
>  mat.[RFM_ID],
>
>  mat.[DESCRIPTION],
>
>  mat.[AMOUNT]
>
> FROM [Y:\LOKASI FILE\FILEPERTAMA.xlsx].[MTRL$] mat
>
>
> UNION ALL
>
>
> SELECT
>
>  UCASE(work.[COMPANY NAME]) AS [COMPANY],
>
>  work.[WO_ID],
>
>  work.[DESCRIPTION],
>
>  work.[AMOUNT]
>
> FROM [Y:\LOKASIFILE\FILEKEDUA.xlsx].[WORK$] work
>
>
> )
>
> GROUP BY [COMPANY]
>
> '===========================
>
> Untuk menampilkan [Grand Total], saya biasanya menggunakan bantuan pivot
> table, tetapi bagaimanakah caranya untuk menampilkan grandtotal dengan
> GROUP BY WITH ROLLUP di SQL? ataukah ada cara lainnya ?
>
>
> Terimakasih,
>
> -zainul
>
>
>
>
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] GROUP BY WITH ROLLUP

Milis Belajar Excel mailing list
In reply to this post by Milis Belajar Excel mailing list

Terimakasih mas Kid.
akan saya coba.

-zainul
#dikirim_pakai_mail.yahoo.com_tanpa_pulsa#a

--------------------------------------------
On Fri, 28/7/17, 'Mr. Kid' [hidden email] [belajar-excel] <[hidden email]> wrote:

 Subject: Re: [belajar-excel] GROUP BY WITH ROLLUP
 To: "BeExcel" <[hidden email]>
 Date: Friday, 28 July, 2017, 7:31 PM
 
 
  
 
 
 
   
 
 
     
       
       
       mas
 Zainul,
 
 opsi RollUp
 adanya di ms sql server.
 koneksi data
 Excel to Excel menggunakan ACE OLE DB yang menggunakan sql
 standar.
 
 1. Biasanya
 begini :
 Select  kolom_key_tipe_text
 , Sum( kolom_yg_diSum )
 From
 tabelnya
 Group By
 kolom_key_tipe_text
 UNION ALL
 Select
 'Grand Total', Sum( kolom_yg_diSum )
 From tabelnya
 
 2. Kalau keynya ada 2
 kolom :
 Select  kolom_key1_tipe_text ,
 kolom_key2_tipe_text , Sum( kolom_yg_diSum )
 From tabelnya
 Group By
 kolom_key1_tipe_text, kolom_key2_tipe_text
 UNION ALL
 Select 'Grand Total' ,
 'Grand Total', Sum( kolom_yg_diSum )
 From tabelnya
 
 3. Kalau ada subtotal per key2 nya
 juga, jadi perlu ditambahin ini :
 UNION
 ALL
 Select
 'SubTotal' , kolom_key2_tipe_text, Sum(
 kolom_yg_diSum )
 From tabelnya
 Group By
 kolom_key2_tipe_text
 
 Contoh
 data di dalam tabel bernama 'tabelnya'
 kolom_key1_tipe_text        
 kolom_key2_tipe_text          
 kolom_yg_diSum  
 A                                                
 X                                     
 10
   
 A                                                
 X                                     
 20
   
 A                                                
 Y
                                    
 400
    B
                                                
 Y
                                   
 5000
 
 Query 0 :
 Select 
 kolom_key1_tipe_text as Nama, kolom_key2_tipe_text as Kota ,
 kolom_yg_diSum as NilaiTotal
 From
 tabelnya
 
 hasilnya : (sama dengan tabel data
 bernama 'tabelnya') dengan nama kolom yang
 ditentukan sendiri
 Nama              
                           Kota        
                      NilaiTotal  
 A                                                
 X                                     
 10
   
 A                                                
 X                                     
 20
   
 A                                                
 Y
                                    
 400
    B
                                                
 Y
                                   
 5000
 
 
 Query 1 :
 Select 
 kolom_key1_tipe_text as Nama, kolom_key2_tipe_text as Kota ,
 Sum( kolom_yg_diSum ) as NilaiTotal
 From
 tabelnya
 Group By kolom_key1_tipe_text,
 kolom_key2_tipe_text
 hasilnya : di grup berdasar kolom
 ke-1 dan ke-2   (2 key) : total setiap nama di setiap
 kota
 Nama             Kota
                             
 NilaiTotal
   
 A                   
 X                                       
 30
   
 A                   
 Y                                     
 400
   
 B                   
 Y                                   
 5000
 
 Query 2 :
 Select 
 kolom_key1_tipe_text as Nama, 'Subtotal Key1' as
 Kota , Sum( kolom_yg_diSum ) as NilaiTotal
 From tabelnya
 Group By
 kolom_key1_tipe_text
 
 hasilnya : subtotal setiap Nama
 Nama             Kota
                             
 NilaiTotal
   
 A                Subtotal Key1          
           430
    B
                Subtotal
 Key1                   5000
 
 Query 3 :
 Select 
 'Subtotal Key2' as Nama, kolom_key2_tipe_text as
 Kota , Sum( kolom_yg_diSum ) as NilaiTotal
 From tabelnya
 Group By
 kolom_key2_tipe_text
 
 hasilnya : subtotal setiap kota
 Nama                     
     Kota                         
   NilaiTotal
    Subtotal Key2
           X          
                             30
    Subtotal
 Key2           Y                      
             5400
 
 Query 4 :
 Select 'Grand Total' as
 Nama , 'Grand Total' as Kota, Sum( kolom_yg_diSum )
 as NilaiTotal
 From tabelnya
 
 Hasilnya : total
 
 Nama                         
 Kota                           
 NilaiTotal
       Grand
 Total             Grand Total  
                  5430
 
 
 Trus masing-masing query yang
 kolom-kolom select-nya sama makna di gabung utuh (UNION
 ALL)
                 
 Query 0
 Union
 All Query
 1
 Union All Query
 2
 Union All Query 3
 Union All Query 4
 
 Hasilnya : ya kaya
 gini
 Nama                     
     Kota                         
   NilaiTotal
   
 A                                                
 X                                      
 10
   
 A                                                
 X                                      
 20
   
 A                                                
 Y
                                    
 400
    B
                                                
 Y
                                  
 5000
   
 A                                   
            
 X                                       
 30
   
 A                                
               
 Y                                     
 400
   
 B                                 
              
 Y                                   
 5000
   
 A                                  
           Subtotal Key1                    
 430
    B
                                            
 Subtotal Key1                   5000
    Subtotal Key2
                             X          
                             30
    Subtotal
 Key2                             Y    
                               5400
       Grand
 Total                             Grand
 Total                    5430
 
 Sila dipilih dan
 diatur sesuai kebutuhan
 
 Regards,
 Kid
 
 
 2017-07-28 7:22 GMT+07:00
 [hidden email]
 [belajar-excel] <[hidden email]>:
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  
 
 
 
   
 
 
     
       
       
       Dear
 Be-Excelers,
 Selain menggunakan pivot table,
 saya menggunakan SQL dengan Microsoft Query di Excel untuk
 merangkum tabel dari beberapa file. Kira-kira
 contohnya seperti di
 ini:'=====================SELECT
 [COMPANY],SUM([AMOUNT]) AS [TOTAL
 AMOUNT] FROM (
 
 SELECT  UCASE(mat.[COMPANY
 NAME]) AS
 [COMPANY], mat.[RFM_ID], mat.[DESCRIPTION],  mat.[AMOUNT]FROM
 [Y:\LOKASI FILE\FILEPERTAMA.xlsx].[MTRL$]
 mat
 UNION ALL
 SELECT  UCASE(work.[COMPANY
 NAME]) AS
 [COMPANY], work.[WO_ID], work.[DESCRIPTION], work.[AMOUNT] FROM
 [Y:\LOKASIFILE\FILEKEDUA.xlsx] .[WORK$]
 work
 ) GROUP BY
 [COMPANY]'===========================Untuk
 menampilkan [Grand Total], saya biasanya menggunakan bantuan
 pivot table, tetapi bagaimanakah caranya untuk
 menampilkan grandtotal dengan GROUP BY WITH ROLLUP di SQL?
 ataukah ada cara lainnya ?
 Terimakasih,-zainul
 
 
 
 
     
     
 
     
     
 
 
 
 
 
 
   
 
 
 
 
 
 
 
 
 
 
     
     
 
     
     
 
 
 
 #yiv1492441926 #yiv1492441926 --
   #yiv1492441926ygrp-mkp {
 border:1px solid #d8d8d8;font-family:Arial;margin:10px
 0;padding:0 10px;}
 
 #yiv1492441926 #yiv1492441926ygrp-mkp hr {
 border:1px solid #d8d8d8;}
 
 #yiv1492441926 #yiv1492441926ygrp-mkp #yiv1492441926hd {
 color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
 0;}
 
 #yiv1492441926 #yiv1492441926ygrp-mkp #yiv1492441926ads {
 margin-bottom:10px;}
 
 #yiv1492441926 #yiv1492441926ygrp-mkp .yiv1492441926ad {
 padding:0 0;}
 
 #yiv1492441926 #yiv1492441926ygrp-mkp .yiv1492441926ad p {
 margin:0;}
 
 #yiv1492441926 #yiv1492441926ygrp-mkp .yiv1492441926ad a {
 color:#0000ff;text-decoration:none;}
 #yiv1492441926 #yiv1492441926ygrp-sponsor
 #yiv1492441926ygrp-lc {
 font-family:Arial;}
 
 #yiv1492441926 #yiv1492441926ygrp-sponsor
 #yiv1492441926ygrp-lc #yiv1492441926hd {
 margin:10px
 0px;font-weight:700;font-size:78%;line-height:122%;}
 
 #yiv1492441926 #yiv1492441926ygrp-sponsor
 #yiv1492441926ygrp-lc .yiv1492441926ad {
 margin-bottom:10px;padding:0 0;}
 
 #yiv1492441926 #yiv1492441926actions {
 font-family:Verdana;font-size:11px;padding:10px 0;}
 
 #yiv1492441926 #yiv1492441926activity {
 background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}
 
 #yiv1492441926 #yiv1492441926activity span {
 font-weight:700;}
 
 #yiv1492441926 #yiv1492441926activity span:first-child {
 text-transform:uppercase;}
 
 #yiv1492441926 #yiv1492441926activity span a {
 color:#5085b6;text-decoration:none;}
 
 #yiv1492441926 #yiv1492441926activity span span {
 color:#ff7900;}
 
 #yiv1492441926 #yiv1492441926activity span
 .yiv1492441926underline {
 text-decoration:underline;}
 
 #yiv1492441926 .yiv1492441926attach {
 clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
 0;width:400px;}
 
 #yiv1492441926 .yiv1492441926attach div a {
 text-decoration:none;}
 
 #yiv1492441926 .yiv1492441926attach img {
 border:none;padding-right:5px;}
 
 #yiv1492441926 .yiv1492441926attach label {
 display:block;margin-bottom:5px;}
 
 #yiv1492441926 .yiv1492441926attach label a {
 text-decoration:none;}
 
 #yiv1492441926 blockquote {
 margin:0 0 0 4px;}
 
 #yiv1492441926 .yiv1492441926bold {
 font-family:Arial;font-size:13px;font-weight:700;}
 
 #yiv1492441926 .yiv1492441926bold a {
 text-decoration:none;}
 
 #yiv1492441926 dd.yiv1492441926last p a {
 font-family:Verdana;font-weight:700;}
 
 #yiv1492441926 dd.yiv1492441926last p span {
 margin-right:10px;font-family:Verdana;font-weight:700;}
 
 #yiv1492441926 dd.yiv1492441926last p
 span.yiv1492441926yshortcuts {
 margin-right:0;}
 
 #yiv1492441926 div.yiv1492441926attach-table div div a {
 text-decoration:none;}
 
 #yiv1492441926 div.yiv1492441926attach-table {
 width:400px;}
 
 #yiv1492441926 div.yiv1492441926file-title a, #yiv1492441926
 div.yiv1492441926file-title a:active, #yiv1492441926
 div.yiv1492441926file-title a:hover, #yiv1492441926
 div.yiv1492441926file-title a:visited {
 text-decoration:none;}
 
 #yiv1492441926 div.yiv1492441926photo-title a,
 #yiv1492441926 div.yiv1492441926photo-title a:active,
 #yiv1492441926 div.yiv1492441926photo-title a:hover,
 #yiv1492441926 div.yiv1492441926photo-title a:visited {
 text-decoration:none;}
 
 #yiv1492441926 div#yiv1492441926ygrp-mlmsg
 #yiv1492441926ygrp-msg p a span.yiv1492441926yshortcuts {
 font-family:Verdana;font-size:10px;font-weight:normal;}
 
 #yiv1492441926 .yiv1492441926green {
 color:#628c2a;}
 
 #yiv1492441926 .yiv1492441926MsoNormal {
 margin:0 0 0 0;}
 
 #yiv1492441926 o {
 font-size:0;}
 
 #yiv1492441926 #yiv1492441926photos div {
 float:left;width:72px;}
 
 #yiv1492441926 #yiv1492441926photos div div {
 border:1px solid
 #666666;min-height:62px;overflow:hidden;width:62px;}
 
 #yiv1492441926 #yiv1492441926photos div label {
 color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}
 
 #yiv1492441926 #yiv1492441926reco-category {
 font-size:77%;}
 
 #yiv1492441926 #yiv1492441926reco-desc {
 font-size:77%;}
 
 #yiv1492441926 .yiv1492441926replbq {
 margin:4px;}
 
 #yiv1492441926 #yiv1492441926ygrp-actbar div a:first-child {
 margin-right:2px;padding-right:5px;}
 
 #yiv1492441926 #yiv1492441926ygrp-mlmsg {
 font-size:13px;font-family:Arial, helvetica, clean,
 sans-serif;}
 
 #yiv1492441926 #yiv1492441926ygrp-mlmsg table {
 font-size:inherit;font:100%;}
 
 #yiv1492441926 #yiv1492441926ygrp-mlmsg select,
 #yiv1492441926 input, #yiv1492441926 textarea {
 font:99% Arial, Helvetica, clean, sans-serif;}
 
 #yiv1492441926 #yiv1492441926ygrp-mlmsg pre, #yiv1492441926
 code {
 font:115% monospace;}
 
 #yiv1492441926 #yiv1492441926ygrp-mlmsg * {
 line-height:1.22em;}
 
 #yiv1492441926 #yiv1492441926ygrp-mlmsg #yiv1492441926logo {
 padding-bottom:10px;}
 
 
 #yiv1492441926 #yiv1492441926ygrp-msg p a {
 font-family:Verdana;}
 
 #yiv1492441926 #yiv1492441926ygrp-msg
 p#yiv1492441926attach-count span {
 color:#1E66AE;font-weight:700;}
 
 #yiv1492441926 #yiv1492441926ygrp-reco
 #yiv1492441926reco-head {
 color:#ff7900;font-weight:700;}
 
 #yiv1492441926 #yiv1492441926ygrp-reco {
 margin-bottom:20px;padding:0px;}
 
 #yiv1492441926 #yiv1492441926ygrp-sponsor #yiv1492441926ov
 li a {
 font-size:130%;text-decoration:none;}
 
 #yiv1492441926 #yiv1492441926ygrp-sponsor #yiv1492441926ov
 li {
 font-size:77%;list-style-type:square;padding:6px 0;}
 
 #yiv1492441926 #yiv1492441926ygrp-sponsor #yiv1492441926ov
 ul {
 margin:0;padding:0 0 0 8px;}
 
 #yiv1492441926 #yiv1492441926ygrp-text {
 font-family:Georgia;}
 
 #yiv1492441926 #yiv1492441926ygrp-text p {
 margin:0 0 1em 0;}
 
 #yiv1492441926 #yiv1492441926ygrp-text tt {
 font-size:120%;}
 
 #yiv1492441926 #yiv1492441926ygrp-vital ul li:last-child {
 border-right:none !important;
 }
 #yiv1492441926
 
Loading...