{"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 varchar(10), Score integer) insert into scores values (&#8216;A&#8217;, 2) go insert into scores values (&#8216;B&#8217;,&hellip;&nbsp;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","pgc_sgb_lightbox_settings":"","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 v28.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\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 varchar(10), Score integer) insert into scores values (&#039;A&#039;, 2) go insert into scores values (&#039;B&#039;,&hellip;&nbsp;\" \/>\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\":\"Article\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=520#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=520\"},\"author\":{\"name\":\"Ray Fox\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\"},\"headline\":\"SQL Server &#8211; subtotals in rows\",\"datePublished\":\"2015-10-16T09:26:36+00:00\",\"dateModified\":\"2018-02-19T10:25:14+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=520\"},\"wordCount\":6,\"publisher\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\"},\"articleSection\":[\"MS SQL Server\"],\"inLanguage\":\"en-GB\"},{\"@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\",\"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\",\"publisher\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\"},\"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\",\"Organization\"],\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\",\"name\":\"Ray Fox\",\"logo\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/image\\\/\"},\"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 varchar(10), Score integer) insert into scores values ('A', 2) go insert into scores values ('B',&hellip;&nbsp;","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":"Article","@id":"https:\/\/dbafox.com\/?p=520#article","isPartOf":{"@id":"https:\/\/dbafox.com\/?p=520"},"author":{"name":"Ray Fox","@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"headline":"SQL Server &#8211; subtotals in rows","datePublished":"2015-10-16T09:26:36+00:00","dateModified":"2018-02-19T10:25:14+00:00","mainEntityOfPage":{"@id":"https:\/\/dbafox.com\/?p=520"},"wordCount":6,"publisher":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"articleSection":["MS SQL Server"],"inLanguage":"en-GB"},{"@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","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","publisher":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"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","Organization"],"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb","name":"Ray Fox","logo":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/www.dbafox.com"]}]}},"_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}]}}