{"id":9045,"date":"2019-02-01T07:04:23","date_gmt":"2019-02-01T07:04:23","guid":{"rendered":"https:\/\/dbtut.com\/?p=9045"},"modified":"2019-02-01T07:04:23","modified_gmt":"2019-02-01T07:04:23","slug":"sum-function-in-sql-servertsql","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/","title":{"rendered":"SUM Function in SQL Server(TSQL)"},"content":{"rendered":"

 <\/p>\n

We use SUM Function to calculate the sum of values in a column. SUM is a aggregated function and generally we use it with GROUP BY Clause. But sometimes we need to calculate to sum of all values in a table.<\/p>\n

You can find the article about GROUP BY Clause below.<\/p>\n

“Group By Clause, SUM, MAX, MIN, AVG, COUNT and COUNT_BIG Functions in SQL Server<\/a>”<\/p>\n

Let’s make examples for a better understanding of SUM Function.<\/p>\n

Example:<\/strong><\/p>\n

First, we create a table with the help of the following script and add a few records into this table.<\/p>\n

CREATE TABLE [dbo].[MyTable]\r\n([ID] INT IDENTITY,\r\n\u00a0[Name] VARCHAR(20) NULL,\r\n\u00a0[Age] INT NULL,\r\n\u00a0[Department] VARCHAR(50) NULL,\r\n\u00a0[Salary] INT NULL)\r\nINSERT [dbo].[MyTable]\r\nVALUES ('Hakan GURBASLAR',36,'Database Department',12000),\r\n\u00a0\u00a0 ('Nurullah CAKIR',34,'Database Department',12000),\r\n\u00a0\u00a0 ('Faruk ERDEM',27,'System Department',9000),\r\n\u00a0\u00a0 ('Ogun Ozalp',27,'System Department',null),\r\n\u00a0\u00a0 ('Tamer Erdogan',43,'System Department',14000)<\/pre>\n
Calculate sum of all staff’s salary:<\/strong><\/p>\n
SELECT SUM(Salary) FROM [dbo].[MyTable]<\/pre>\n
\"\"<\/p>\n
Group the staffs accoding to their department and calculate sum of each group’s salary:<\/strong><\/p>\n
SELECT [Department],SUM(Salary) FROM [dbo].[MyTable]\r\nGROUP BY [Department]<\/pre>\n
\"\"<\/p>\n
We can use DISTINCT or ALL Clause with SUM Function. The default value is ALL.<\/p>\n
If you want to calculate sum of distinct values in a dataset you can use DISTINCT Clause as follows. As you know there is two staff with the same salary in the table. First look at the all records in the table.<\/p>\n
SELECT * FROM [dbo].[MyTable]<\/pre>\n
\"\"<\/p>\n
Then Execute the previous query with the DISTINCT Clause as follows:<\/strong><\/p>\n
As a result, sum of Database Department’s Salary will return 12000, because there is two record with the same value in this group.<\/p>\n
SELECT [Department],SUM (DISTINCT Salary)\u00a0 FROM [dbo].[MyTable]\r\nGROUP BY [Department]<\/pre>\n
\"\"<\/p>\n
Finally, you can use SUM Function with OVER Clause. You can find necessary details in the article “OVER Clause in SQL Server(TSQL)<\/a>“<\/p>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
  We use SUM Function to calculate the sum of values in a column. SUM is a aggregated function and generally we use it with GROUP BY Clause. But sometimes we need to calculate to sum of all values in a table. You can find the article about GROUP BY Clause below. “Group By Clause, …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3,1596],"tags":[1878,1879],"class_list":["post-9045","post","type-post","status-publish","format-standard","","category-mssql","category-tsql","tag-sum-function-in-tsql","tag-sum-function-with-distinct"],"aioseo_notices":[],"yoast_head":"\nSUM Function in SQL Server(TSQL) - Database Tutorials<\/title>\n<meta name=\"description\" content=\"SUM Function in SQL Server(TSQL)\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SUM Function in SQL Server(TSQL) - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"SUM Function in SQL Server(TSQL)\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-02-01T07:04:23+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c53ee919707e.png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"SUM Function in SQL Server(TSQL)\",\"datePublished\":\"2019-02-01T07:04:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/\"},\"wordCount\":240,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c53ee919707e.png\",\"keywords\":[\"Sum Function in TSQL\",\"Sum Function with Distinct\"],\"articleSection\":[\"MSSQL\",\"TSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/\",\"name\":\"SUM Function in SQL Server(TSQL) - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c53ee919707e.png\",\"datePublished\":\"2019-02-01T07:04:23+00:00\",\"description\":\"SUM Function in SQL Server(TSQL)\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c53ee919707e.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c53ee919707e.png\",\"width\":595,\"height\":215},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SUM Function in SQL Server(TSQL)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SUM Function in SQL Server(TSQL) - Database Tutorials","description":"SUM Function in SQL Server(TSQL)","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:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/","og_locale":"en_US","og_type":"article","og_title":"SUM Function in SQL Server(TSQL) - Database Tutorials","og_description":"SUM Function in SQL Server(TSQL)","og_url":"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/","og_site_name":"Database Tutorials","article_published_time":"2019-02-01T07:04:23+00:00","og_image":[{"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c53ee919707e.png","type":"","width":"","height":""}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"SUM Function in SQL Server(TSQL)","datePublished":"2019-02-01T07:04:23+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/"},"wordCount":240,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c53ee919707e.png","keywords":["Sum Function in TSQL","Sum Function with Distinct"],"articleSection":["MSSQL","TSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/","url":"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/","name":"SUM Function in SQL Server(TSQL) - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c53ee919707e.png","datePublished":"2019-02-01T07:04:23+00:00","description":"SUM Function in SQL Server(TSQL)","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c53ee919707e.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c53ee919707e.png","width":595,"height":215},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/01\/sum-function-in-sql-servertsql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"SUM Function in SQL Server(TSQL)"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/9045","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=9045"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/9045\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=9045"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=9045"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=9045"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}