{"id":520,"date":"2015-10-16T09:26:36","date_gmt":"2015-10-16T09:26:36","guid":{"rendered":"http:\/\/www.dbafox.com\/?p=520"},"modified":"2018-02-19T11:25:14","modified_gmt":"2018-02-19T10:25:14","slug":"sql-server-subtotals-in-rows","status":"publish","type":"post","link":"https:\/\/dbafox.com\/?p=520","title":{"rendered":"SQL Server &#8211; subtotals in rows"},"content":{"rendered":"<pre>\r\nHow to add a subtotal row in sql\r\nThis comes from: http:\/\/stackoverflow.com\/questions\/19387650\/how-to-add-a-subtotal-row-in-sql\r\n\r\nName Score\r\nA     2\r\nB     3\r\nA     1\r\nB     3\r\n\r\nName Score\r\nA     2\r\nA     1\r\nSubtotal 3\r\nB    3\r\nB    3\r\nSubtotal 6\r\n\r\n\r\ncreate table scores (name varchar(10), Score integer)\r\n\r\ninsert into scores values ('A', 2)\r\ngo\r\ninsert into scores values ('B', 3)\r\ngo\r\ninsert into scores values ('A', 1)\r\ngo\r\ninsert into scores values ('B', 3)\r\ngo\r\n\r\n\r\n\r\nwith cte as (\r\n    select *, row_number() over(order by newid()) as rn\r\n    from scores\r\n)\r\nselect\r\n    case\r\n       when grouping(c.rn) = 1 then 'Subtotal'\r\n       else c.Name\r\n    end as Name,\r\n    sum(c.Score) as Score\r\nfrom cte as c\r\ngroup by grouping sets ((c.Name), (c.Name, c.rn))\r\norder by c.Name;\r\n\r\n-- OR\r\n\r\nwith cte as (\r\n    select *, row_number() over(order by newid()) as rn\r\n    from scores\r\n)\r\nselect\r\n    case\r\n       when grouping(c.rn) = 1 then 'Subtotal'\r\n       else c.Name\r\n    end as Name,\r\n    sum(c.Score) as Score\r\nfrom cte as c\r\ngroup by rollup(c.Name, c.rn)\r\nhaving grouping(c.Name) = 0\r\norder by c.Name;\r\n\r\n\r\noracle equivalent on the same scores table:\r\n\r\nwith cte as (\r\n    select scores.*, row_number() over(order by 2) as rn\r\n    from scores\r\n)\r\nselect\r\n    case\r\n       when grouping(c.rn) = 1 then 'Subtotal'\r\n       else c.Name\r\n    end as Name,\r\n    sum(c.Score) as Score\r\nfrom cte c\r\ngroup by grouping sets ((c.Name), (c.Name, c.rn))\r\norder by c.Name\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>How to add a subtotal row in sql This comes from: http:\/\/stackoverflow.com\/questions\/19387650\/how-to-add-a-subtotal-row-in-sql Name Score A 2 B 3 A 1 B 3 Name Score A 2 A 1 Subtotal 3 B 3 B 3 Subtotal 6 create table scores (name &hellip; <a href=\"https:\/\/dbafox.com\/?p=520\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"pgc_sgb_lightbox_settings":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[24],"tags":[],"class_list":["post-520","post","type-post","status-publish","format-standard","hentry","category-ms-sql-server"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.6 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQL Server - subtotals in rows - dbafox<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbafox.com\/?p=520\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server - subtotals in rows - dbafox\" \/>\n<meta property=\"og:description\" content=\"How to add a subtotal row in sql This comes from: http:\/\/stackoverflow.com\/questions\/19387650\/how-to-add-a-subtotal-row-in-sql Name Score A 2 B 3 A 1 B 3 Name Score A 2 A 1 Subtotal 3 B 3 B 3 Subtotal 6 create table scores (name &hellip; Continue reading &rarr;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbafox.com\/?p=520\" \/>\n<meta property=\"og:site_name\" content=\"dbafox\" \/>\n<meta property=\"article:published_time\" content=\"2015-10-16T09:26:36+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-02-19T10:25:14+00:00\" \/>\n<meta name=\"author\" content=\"Ray Fox\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ray Fox\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated 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\":\"WebPage\",\"@id\":\"https:\/\/dbafox.com\/?p=520\",\"url\":\"https:\/\/dbafox.com\/?p=520\",\"name\":\"SQL Server - subtotals in rows - dbafox\",\"isPartOf\":{\"@id\":\"https:\/\/dbafox.com\/#website\"},\"datePublished\":\"2015-10-16T09:26:36+00:00\",\"dateModified\":\"2018-02-19T10:25:14+00:00\",\"author\":{\"@id\":\"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb\"},\"breadcrumb\":{\"@id\":\"https:\/\/dbafox.com\/?p=520#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbafox.com\/?p=520\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbafox.com\/?p=520#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbafox.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server &#8211; subtotals in rows\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbafox.com\/#website\",\"url\":\"https:\/\/dbafox.com\/\",\"name\":\"dbafox\",\"description\":\"DBA Technical Information\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbafox.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb\",\"name\":\"Ray Fox\",\"sameAs\":[\"http:\/\/www.dbafox.com\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server - subtotals in rows - dbafox","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":"https:\/\/dbafox.com\/?p=520","og_locale":"en_GB","og_type":"article","og_title":"SQL Server - subtotals in rows - dbafox","og_description":"How to add a subtotal row in sql This comes from: http:\/\/stackoverflow.com\/questions\/19387650\/how-to-add-a-subtotal-row-in-sql Name Score A 2 B 3 A 1 B 3 Name Score A 2 A 1 Subtotal 3 B 3 B 3 Subtotal 6 create table scores (name &hellip; Continue reading &rarr;","og_url":"https:\/\/dbafox.com\/?p=520","og_site_name":"dbafox","article_published_time":"2015-10-16T09:26:36+00:00","article_modified_time":"2018-02-19T10:25:14+00:00","author":"Ray Fox","twitter_misc":{"Written by":"Ray Fox","Estimated reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/dbafox.com\/?p=520","url":"https:\/\/dbafox.com\/?p=520","name":"SQL Server - subtotals in rows - dbafox","isPartOf":{"@id":"https:\/\/dbafox.com\/#website"},"datePublished":"2015-10-16T09:26:36+00:00","dateModified":"2018-02-19T10:25:14+00:00","author":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"breadcrumb":{"@id":"https:\/\/dbafox.com\/?p=520#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbafox.com\/?p=520"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbafox.com\/?p=520#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbafox.com\/"},{"@type":"ListItem","position":2,"name":"SQL Server &#8211; subtotals in rows"}]},{"@type":"WebSite","@id":"https:\/\/dbafox.com\/#website","url":"https:\/\/dbafox.com\/","name":"dbafox","description":"DBA Technical Information","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbafox.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":"Person","@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb","name":"Ray Fox","sameAs":["http:\/\/www.dbafox.com"]}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3ecMb-8o","_links":{"self":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/520","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=520"}],"version-history":[{"count":7,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/520\/revisions"}],"predecessor-version":[{"id":1163,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/520\/revisions\/1163"}],"wp:attachment":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=520"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=520"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=520"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}