# When to use Count and when to use Sum

### Question

• How do you know when you should use COUNT and when to use SUM functions?
• Moved by Thursday, June 20, 2013 11:58 PM Moved to a more appropriate forum
Thursday, June 20, 2013 11:24 PM

• ```CREATE TABLE #t1
(empid int, deptname varchar(100), sal numeric(10,2))
INSERT INTO #t1 VALUES (1, 'Sales', 10000)
,(1, 'Sales', 20000)
,(2, 'Production', 30000)
,(3, 'Sales', 40000)
,(4, 'Sales', 50000)
,(5, 'Production', 60000)
SELECT deptname, COUNT(empid) FROM #t1 GROUP BY deptname
SELECT deptname, SUM(sal) as totalsal FROM #t1 GROUP BY deptname
DROP TABLE #t1```

COUNT counts the total number of employees in each department
SUM provides total salary for the each department

Best Luck, Shenoy

• Marked as answer by Friday, June 21, 2013 1:59 PM
Friday, June 21, 2013 12:45 PM

### All replies

• The question does not look like a valid one. Whenever you want COUNT of items, use COUNT.

Try the below to understand better:

create table T11(Col int)

Insert into T11 Values (100),(200)

Select count(Col1) From T11

Select Sum(Col1) From T11

Friday, June 21, 2013 12:28 AM
• Don't you know how to count or do addition? If not, you should not be programming ...

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Friday, June 21, 2013 12:48 AM
• COUNT (Transact-SQL)
SQL Server 2012

Returns the number of items in a group. COUNT works like the COUNT_BIG function. The only difference between the two functions is their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value. May be followed by the OVER clause.

SUM (Transact-SQL)
SQL Server 2012

Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored. May be followed by the OVER Clause (Transact-SQL).

Many Thanks & Best Regards, Hua Min

Friday, June 21, 2013 1:50 AM
• How do you know when you should use COUNT and when to use SUM functions?

COUNT: Let us say there is an EMPLOYEE Table

CASE-1: How many employees are there is an organisation

`SELECT COUNT(*) FROM EMPLOYEE`

CASE-2: What is the total amount spending for all the employees in an organisation.

`SELECT SUM(SAL) FROM EMPLOYEE`

Thanks.

bala krishna

Friday, June 21, 2013 4:10 AM
• Count() function gives you no of count in the table like row count.

Sum() function gives you total of column values like SUM(quantity)

please try this simple script its enough to understand.

```create table test
(
id int identity,
quantity int
)

insert into test values(30)
insert into test values(10)
insert into test values(15)

select * from test

select SUM(quantity) from test
select COUNT(quantity) from test```

Friday, June 21, 2013 4:33 AM
• Count Returns the number of items in a group.

http://msdn.microsoft.com/en-us/library/ms175997.aspx

SUM Returns the sum of all the values, or only the DISTINCT values, in the expression.

Friday, June 21, 2013 5:00 AM
• The question does not look like a valid one. Whenever you want COUNT of items, use COUNT.

Try the below to understand better:

create table T11(Col int)

Insert into T11 Values (100),(200)

Select count(Col1) From T11

Select Sum(Col1) From T11

Sorry, let me elaborate more.  I know to use SUM when you want to perform calculations, such as SUM(BigMacPrice + HappyMealPrice + FishFiletComboPrice), but I was running this commands and didn't see why Sum worked but COUNT didn't

```Create table Data
(
name varchar(100)
ID int
)

Insert Into Data(name, ID)
Select name, Count(ID)
FROM SeattleDB
Group By name
Insert Into Data(name, ID)
Select name, Count(ID)
FROM HoustonDB
Group By name

--this is why I was asking the question
--this statement returned inaccurate results
Select name, COUNT(ID)
From Data
Group By Name

--however this one returned accurate and was trying to
--fathom why
Select name, SUM(ID)
From Data
Group By Name```

Friday, June 21, 2013 11:49 AM
• Because you are already counting while inserting into DATA table.

Try the below:(Not sure of your other table SeattleDB and HoustonDB)

```Create table Data
(
name varchar(100),
ID int
)

Insert Into Data(name, ID)
Select name, ID
FROM SeattleDB

Insert Into Data(name, ID)
Select name, ID
FROM HoustonDB

Select name, COUNT(ID)
From Data
Group By Name

Select name, SUM(ID)
From Data
Group By Name```

Friday, June 21, 2013 11:57 AM
• Because COUNT does exactly what it says, it is counting the number of instances. You've already aggregated the data with count in the inserts.

SUM will return the SUM of all the rows explicitly expressed in your query.

Friday, June 21, 2013 11:58 AM
• Count - No Of Records

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name;

Sum - Total of All values in a column in all records

The SUM() function returns the total sum of a numeric column.

### SQL SUM() Syntax

SELECT SUM(column_name) FROM table_name;

Refer Article

Regards

Mohit Gupta

Friday, June 21, 2013 12:19 PM
• ```CREATE TABLE #t1
(empid int, deptname varchar(100), sal numeric(10,2))
INSERT INTO #t1 VALUES (1, 'Sales', 10000)
,(1, 'Sales', 20000)
,(2, 'Production', 30000)
,(3, 'Sales', 40000)
,(4, 'Sales', 50000)
,(5, 'Production', 60000)
SELECT deptname, COUNT(empid) FROM #t1 GROUP BY deptname
SELECT deptname, SUM(sal) as totalsal FROM #t1 GROUP BY deptname
DROP TABLE #t1```

COUNT counts the total number of employees in each department
SUM provides total salary for the each department

Best Luck, Shenoy

• Marked as answer by Friday, June 21, 2013 1:59 PM
Friday, June 21, 2013 12:45 PM
• What is the purpose for your stupid answer? And to make it worse you have the nerve to show your face.
Wednesday, May 06, 2015 7:14 PM