CODEWITHSUNDEEP
sqlsql-server-2008-r2
TheProvost
TheProvost

Reputation: 1893

Get Sum Of All Transactions Per Month Including Months With No Transaction

To simplify table structure, I am going to use a simple table sample. Supposing I have this table:

+------+------------+-------------+
| id   | date       | Quantity    |
+------+------------+-------------+
|    1 | 2014-01-01 |          10 |
|    2 | 2014-01-20 |          20 |
|    3 | 2014-02-03 |          30 |
|    4 | 2014-02-28 |          40 |
|    5 | 2014-06-01 |          50 |
|    6 | 2014-06-13 |          24 |
|    7 | 2014-12-12 |          45 |
|    8 | 2014-12-18 |          10 |
+------+------------+-------------+

I need to get the sum of Quantity per month including months with no transaction

I have tried this, but it only displays months with transactions.

Select Month(date), Sum(Quantity) from tablename Group By Month(date)

Do not mind the year. Lets just say the table only contains current year data

Upvotes: 1

Views: 1488

Answers (3)

Ormoz
Ormoz

Reputation: 3013

Create a dummy table storing the months:

Select months.m, isnull(Sum(Quantity),0) as Q 
from tablename right join 
(VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) 
AS months(m)
on months.m=Month(tablename.date)
Group By months.m

Here is a fiddle

Upvotes: 2

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

With Months as
(
  SELECT 1 AS MNTH
  UNION ALL
  SELECT MNTH+1 FROM Months WHERE MNTH<12
)

SELECT MONTH(MNTH),SUM(QUANTITY) 
FROM Months M LEFT JOIN TABLENAME T ON M.MNTH = MONTH(T.DATE)
GROUP BY MONTH(MNTH)

Upvotes: 0

shawnt00
shawnt00

Reputation: 17935

This is the general idea. The key is to create a table listing the months you need and then attach the real data with an outer join.

with months(mon) as (
    select  1 union all select  2 union all select  3 union all
    select  4 union all select  5 union all select  6 union all
    select  7 union all select  8 union all select  9 union all
    select 10 union all select 11 union all select 12
)
select m.mon, coalesce(sum(quantity), 0) as quantity
from months m left outer join <T> t on month(t.date) = m.mon
group by m.mon

There are lots of ways to create the list of months if you need more than a year of data from January to December or don't like this approach.

Upvotes: 0

Related Questions