none
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 Bob BeaucheminMVP Thursday, June 20, 2013 11:58 PM Moved to a more appropriate forum
    Thursday, June 20, 2013 11:24 PM

Answers

  • 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 IndigoMontoya 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.

    Whenever you want the SUM of items, use SUM. Please give more clarification on your question to help you better.

    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


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    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.

    See below URL For more information on COUNT 

    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.

    See below URL For more information on SUM

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


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

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

    Whenever you want the SUM of items, use SUM. Please give more clarification on your question to help you better.

    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


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    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


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    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 IndigoMontoya Friday, June 21, 2013 1:59 PM
    Friday, June 21, 2013 12:45 PM