{"id":147,"date":"2014-01-12T21:29:25","date_gmt":"2014-01-12T13:29:25","guid":{"rendered":"http:\/\/suherman.asia\/w2\/?p=147"},"modified":"2014-01-12T21:35:02","modified_gmt":"2014-01-12T13:35:02","slug":"data-rollup","status":"publish","type":"post","link":"http:\/\/suherman.asia\/w2\/data-rollup.html","title":{"rendered":"Data Rollup"},"content":{"rendered":"<p>Kadangkala saat kita bekerja dengan banyak data, muncul kebutuhan untuk dapat menjumlahkan (SUM) nilai dari beberapa record namun berasal dari hasil grouping. Misal pada sample yang saya bawakan ini menggunakan database Adventureworks.<\/p>\n<p><!--more--><\/p>\n<p>&nbsp;<\/p>\n<p>Untuk mempermudah pengertian Data Rollup, kita persiapkan sebuah view yang menggambarkan hasil penjualan product-product yang dikelompokkan berdasarkan Category, Sub Category dan kelompok Product<\/p>\n<p><a href=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/image.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/image_thumb.png\" width=\"698\" height=\"200\" border=\"0\" \/><\/a><\/p>\n<p>Sehingga pada saat kita panggil view Sales.vSalesByCategory datanya akan tampak sebagai berikut :<\/p>\n<p><a href=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/image1.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/image_thumb1.png\" width=\"363\" height=\"468\" border=\"0\" \/><\/a><\/p>\n<p>Misalkan kita ingin mendapatkan nilai total penjualan untuk setiap product dapat dilakukan dengan melakukan Query berikut :<\/p>\n<p><a href=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/image2.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/image_thumb2.png\" width=\"388\" height=\"450\" border=\"0\" \/><\/a><\/p>\n<p>Hasil query diatas menunjukkan total penjualan setiap barang. lalu bagaimana jika kita ingin tahu nilai penjualan yang dikelompokkan berdasarakan Category, Category dan Sub Category, Category dan Subcategory dan Product ??<\/p>\n<p>Nah disinilah peran dari data ROLLUP, dengan menyisipkan klausa WITH ROLLUP seperti berikut :<\/p>\n<p><a href=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/image3.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/image_thumb3.png\" width=\"303\" height=\"365\" border=\"0\" \/><\/a><\/p>\n<p>Sehingga data yang dihasilkan akan tampak seperti berikut :<\/p>\n<p><a href=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/image4.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/image_thumb4.png\" width=\"478\" height=\"155\" border=\"0\" \/><\/a><\/p>\n<p>Pada data yang mengalami nilai Category, subCategory serta Product nya NULL ini mengartikan bahwa total penjualan seluruh category adalah 140707584.8246<\/p>\n<p>kemudian pada Category \u2018Accessories\u2019 yang memiliki nilai SubCategory dan Product nya NULL mengartikan bahwa total penjualan untuk seluruh Category \u2018Accessories\u2019 adalah 63583321.6767<\/p>\n<p>Kemudian Nilai total Penjualan subCategory \u2018Bike Racks\u2019 untuk category \u2018Accessories\u2019 adalah 1210260.9025<\/p>\n<p>dan demikian seterusnya.<\/p>\n<p>Demikianlah pembahasan simple mengenai Data ROLLUP, semoga bermanfaat.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Kadangkala saat kita bekerja dengan banyak data, muncul kebutuhan untuk dapat menjumlahkan (SUM) nilai dari beberapa record namun berasal dari hasil grouping. Misal pada sample yang saya bawakan ini menggunakan database Adventureworks.<\/p>\n<p class=\"continue-reading-button\"> <a class=\"continue-reading-link\" href=\"http:\/\/suherman.asia\/w2\/data-rollup.html\">Continue reading<i class=\"crycon-right-dir\"><\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":150,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[14],"class_list":["post-147","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-t-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.7 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Data Rollup - Suherman Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/suherman.asia\/w2\/data-rollup.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Data Rollup - Suherman Blog\" \/>\n<meta property=\"og:description\" content=\"Kadangkala saat kita bekerja dengan banyak data, muncul kebutuhan untuk dapat menjumlahkan (SUM) nilai dari beberapa record namun berasal dari hasil grouping. Misal pada sample yang saya bawakan ini menggunakan database Adventureworks. Continue reading\" \/>\n<meta property=\"og:url\" content=\"http:\/\/suherman.asia\/w2\/data-rollup.html\" \/>\n<meta property=\"og:site_name\" content=\"Suherman Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-12T13:29:25+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-01-12T13:35:02+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png\" \/>\n\t<meta property=\"og:image:width\" content=\"424\" \/>\n\t<meta property=\"og:image:height\" content=\"134\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\/\/suherman.asia\/w2\/data-rollup.html#article\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/data-rollup.html\"},\"author\":{\"name\":\"admin\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"headline\":\"Data Rollup\",\"datePublished\":\"2014-01-12T13:29:25+00:00\",\"dateModified\":\"2014-01-12T13:35:02+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/suherman.asia\/w2\/data-rollup.html\"},\"wordCount\":199,\"commentCount\":1,\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"image\":{\"@id\":\"http:\/\/suherman.asia\/w2\/data-rollup.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png\",\"keywords\":[\"T-SQL\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\/\/suherman.asia\/w2\/data-rollup.html#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/suherman.asia\/w2\/data-rollup.html\",\"url\":\"http:\/\/suherman.asia\/w2\/data-rollup.html\",\"name\":\"Data Rollup - Suherman Blog\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\/\/suherman.asia\/w2\/data-rollup.html#primaryimage\"},\"image\":{\"@id\":\"http:\/\/suherman.asia\/w2\/data-rollup.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png\",\"datePublished\":\"2014-01-12T13:29:25+00:00\",\"dateModified\":\"2014-01-12T13:35:02+00:00\",\"breadcrumb\":{\"@id\":\"http:\/\/suherman.asia\/w2\/data-rollup.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/suherman.asia\/w2\/data-rollup.html\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/data-rollup.html#primaryimage\",\"url\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png\",\"contentUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png\",\"width\":424,\"height\":134},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/suherman.asia\/w2\/data-rollup.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/suherman.asia\/w2\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Data Rollup\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/suherman.asia\/w2\/#website\",\"url\":\"http:\/\/suherman.asia\/w2\/\",\"name\":\"Suherman Blog\",\"description\":\"Just Another Geek Site\",\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/suherman.asia\/w2\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"caption\":\"admin\"},\"logo\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\"},\"sameAs\":[\"http:\/\/suherman.asia\",\"https:\/\/www.facebook.com\/emantin34\",\"https:\/\/x.com\/emantin34\"],\"url\":\"http:\/\/suherman.asia\/w2\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Data Rollup - Suherman Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/suherman.asia\/w2\/data-rollup.html","og_locale":"en_US","og_type":"article","og_title":"Data Rollup - Suherman Blog","og_description":"Kadangkala saat kita bekerja dengan banyak data, muncul kebutuhan untuk dapat menjumlahkan (SUM) nilai dari beberapa record namun berasal dari hasil grouping. Misal pada sample yang saya bawakan ini menggunakan database Adventureworks. Continue reading","og_url":"http:\/\/suherman.asia\/w2\/data-rollup.html","og_site_name":"Suherman Blog","article_publisher":"https:\/\/www.facebook.com\/emantin34","article_author":"https:\/\/www.facebook.com\/emantin34","article_published_time":"2014-01-12T13:29:25+00:00","article_modified_time":"2014-01-12T13:35:02+00:00","og_image":[{"width":424,"height":134,"url":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png","type":"image\/png"}],"author":"admin","twitter_misc":{"Written by":"admin","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/suherman.asia\/w2\/data-rollup.html#article","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/data-rollup.html"},"author":{"name":"admin","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"headline":"Data Rollup","datePublished":"2014-01-12T13:29:25+00:00","dateModified":"2014-01-12T13:35:02+00:00","mainEntityOfPage":{"@id":"http:\/\/suherman.asia\/w2\/data-rollup.html"},"wordCount":199,"commentCount":1,"publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"image":{"@id":"http:\/\/suherman.asia\/w2\/data-rollup.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png","keywords":["T-SQL"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/suherman.asia\/w2\/data-rollup.html#respond"]}]},{"@type":"WebPage","@id":"http:\/\/suherman.asia\/w2\/data-rollup.html","url":"http:\/\/suherman.asia\/w2\/data-rollup.html","name":"Data Rollup - Suherman Blog","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/#website"},"primaryImageOfPage":{"@id":"http:\/\/suherman.asia\/w2\/data-rollup.html#primaryimage"},"image":{"@id":"http:\/\/suherman.asia\/w2\/data-rollup.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png","datePublished":"2014-01-12T13:29:25+00:00","dateModified":"2014-01-12T13:35:02+00:00","breadcrumb":{"@id":"http:\/\/suherman.asia\/w2\/data-rollup.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/suherman.asia\/w2\/data-rollup.html"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/data-rollup.html#primaryimage","url":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png","contentUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png","width":424,"height":134},{"@type":"BreadcrumbList","@id":"http:\/\/suherman.asia\/w2\/data-rollup.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/suherman.asia\/w2"},{"@type":"ListItem","position":2,"name":"Data Rollup"}]},{"@type":"WebSite","@id":"http:\/\/suherman.asia\/w2\/#website","url":"http:\/\/suherman.asia\/w2\/","name":"Suherman Blog","description":"Just Another Geek Site","publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/suherman.asia\/w2\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","caption":"admin"},"logo":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/suherman.asia","https:\/\/www.facebook.com\/emantin34","https:\/\/x.com\/emantin34"],"url":"http:\/\/suherman.asia\/w2\/author\/admin"}]}},"_links":{"self":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/147","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/comments?post=147"}],"version-history":[{"count":3,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/147\/revisions"}],"predecessor-version":[{"id":152,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/147\/revisions\/152"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media\/150"}],"wp:attachment":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media?parent=147"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/categories?post=147"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/tags?post=147"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}