Terimakasih mas Kid.
akan saya coba.
-zainul
#dikirim_pakai_mail.yahoo.com_tanpa_pulsa#a
--------------------------------------------
On Fri, 28/7/17, 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
<belajar-excel@yahoogroups.com> wrote:
Subject: Re: [belajar-excel] GROUP BY WITH ROLLUP
To: "BeExcel" <belajar-excel@yahoogroups.com>
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
zainul_u...@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com>:
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
