CODEWITHSUNDEEP
sqlsql-servert-sql
Talbert1209
Talbert1209

Reputation: 175

Difference between COUNT and SUM within an Aggregate CASE statement

Full disclosure, I am learning and I have searched all over the internet and I just can't figure out my question.

I am working on an online class and was given the following example:

select 
     DATENAME(MONTH, DATEADD(MONTH, MONTH(OrderDate), -1)) AS  'Month',
     SUM(CASE WHEN YEAR(OrderDate) = 2005 THEN 1 ELSE 0 END) AS Orders,
     SUM(CASE YEAR(OrderDate) WHEN 2005 THEN Totaldue ELSE 0 END) AS 'Total Value'
from
    sales.salesorderheader
group by Month(orderdate)
order by Month(orderdate) ASC

That returns the following results:

Results 1

I understood that (I thought) so I began messing around with the code to further understand Case statements. Looking at the code I thought that the Orders field was essentially finding all the orders in a month, assigning a 1 to each one, and then adding them all up. Because each one was assigned a 1 I figured that I could change the SUM to COUNT and I would get the same results.

However, this code:

select 
     DATENAME(MONTH, DATEADD(MONTH, MONTH(OrderDate), -1)) AS  'Month',
     COUNT(CASE WHEN YEAR(OrderDate) = 2005 THEN 1 ELSE 0 END) AS Orders,
     SUM(CASE YEAR(OrderDate) WHEN 2005 THEN Totaldue ELSE 0 END) AS 'Total Value'
from
    sales.salesorderheader
group by Month(orderdate)
order by Month(orderdate) ASC

Returns these results:

Results 2

To try and break this down I created a query that would just look for the orders in January 2005 and count them.

SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate >= '1/1/2005' AND OrderDate < '1/1/2005'

This returned 0. The same as the SUM query. I get that COUNT counts rows and SUM sums numbers in a column, but I just don't understand the results I'm getting. Could someone please explain why the count query is returning 2483 for January and not 0?

Upvotes: 1

Views: 4642

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175616

For COUNT 1 and 0 are the same. What you really need is NULL:

COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

select 
   DATENAME(MONTH, DATEADD(MONTH, MONTH(OrderDate), -1)) AS  'Month',
   COUNT(CASE WHEN YEAR(OrderDate) = 2005 THEN 1 ELSE NULL END) AS Orders,
   SUM(CASE YEAR(OrderDate) WHEN 2005 THEN Totaldue ELSE 0 END) AS 'Total Value'
from sales.salesorderheader
group by Month(orderdate)
order by Month(orderdate) ASC;

Or even shorter(default ELSE is NULL so we could omit that part)

COUNT(CASE WHEN YEAR(OrderDate) = 2005 THEN 1 END) AS Orders,

Example: 

              SUM         COUNT         COUNT
2005           1            1             1
2006           0            0            NULL
2007           0            0            NULL
2005           1            1             1
===============================================
               2            4             2

Upvotes: 8

Akankha Ahmed
Akankha Ahmed

Reputation: 121

count example: assume that your column has 3 value and column name is the order

2 ---------- 5 ---------- 4----- Null

now if you run

count (order)

it will return = 3 how many entries you have in the column without null

sum example:

2 ---------- 5 ---------- 4

now if you run

sum (order)

it will return = 2+5+4=11 its add all the entries

Upvotes: 0

Renato Afonso
Renato Afonso

Reputation: 654

When you use count(*) you count ALL the rows. If you want to count how many orders you have, you have to use a column: eg: count(OrderDate). Try it

Upvotes: 0

Related Questions