-1
state    month          ID      sales
-------------------------------------
FL       05/18/2015     0001   12,000
FL       05/19/2015     0001    6,000
FL       05/20/2015     0001    3,000
FL       05/21/2015     0001    6,000
FL       06/01/2016     0001    4,000  
TX       06/02/2016     0050    1,000

In the above table month column having same month but with different date. My question is how to add/sum of sales column having same month with different dates?

Arulkumar
  • 12,966
  • 14
  • 47
  • 68
user3558942
  • 1
  • 1

6 Answers6

2

try this way

select datepart(year,month) as year ,datepart(month,month) as month,  sum(sales)
from tablename
group by datepart(year,month) ,datepart(month,month)
Mukesh Kalgude
  • 4,814
  • 2
  • 17
  • 32
0

Use a simple aggregation on the month column. You can use the DATENAME function to extract the month given a date.

SELECT  YEAR([month]), DATENAME(MONTH, [month]) AS Month,
        SUM(sales)
FROM    mytable
GROUP BY YEAR([month]), DATENAME(MONTH, [month]);
Chris Pickford
  • 8,642
  • 5
  • 42
  • 73
0

first see this question on how to convert the month column (assuming it's a string/varchar) to a date. Next make a sum() of the sales column grouping by the month and year part of the date.

Community
  • 1
  • 1
Ruben Verschueren
  • 822
  • 13
  • 28
0

Are you saying for you want the totals for January .. December irrespective of the year?

If yes then you could do a simple agrregate and group by DATEPART(month, theDate)

EG

SELECT STATE, DATEPART(month, [month]), SUM(sales)
FROM yourTable
GROUP BY 
  STATE, DATEPART(month, [month])
AntDC
  • 1,807
  • 14
  • 23
0

By using the below query you can get the result as month, year wise:

SELECT MONTH([Month]) [Month], YEAR([Month]) [Year], SUM(Sales) TotalSales
FROM TestTable
GROUP BY MONTH([Month]), YEAR([Month])
ORDER BY MONTH([Month]), YEAR([Month])

Actual execution with given sample data:

DECLARE @TestTable TABLE ([State] VARCHAR (20), [Month] DATE, ID VARCHAR(5), Sales INT);

INSERT INTO @TestTable ([State], [Month], ID, Sales)
VALUES 
('FL', '05/18/2015', '0001', 12000),
('FL', '05/19/2015', '0001',  6000),
('FL', '05/20/2015', '0001',  3000),
('FL', '05/21/2015', '0001',  6000),
('FL', '06/01/2016', '0001',  4000),  
('TX', '06/02/2016', '0050',  1000),
('TX', '05/02/2016', '0050',  1000)

SELECT MONTH([Month]) [Month], YEAR([Month]) [Year], SUM(Sales) TotalSales
FROM @TestTable
GROUP BY MONTH([Month]), YEAR([Month])
ORDER BY MONTH([Month]), YEAR([Month])

Result:

Month   Year    TotalSales
5      2015     27000
5      2016      1000
6      2016      5000
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
0

Below query might help you :-

declare @test111 table
(name varchar(20) NOT NULL,
month date NOT NULL,
col1 int NOT NULL,
sales int NOT NULL)

Insert into @test111
values('FL','05/18/2015',0001,12000),
('FL','05/19/2015',0001,6000),
('FL','05/20/2015',0001,3000),
('FL','05/21/2015',0001,6000),
('FL','06/01/2016',0001,4000),
('TX','06/02/2016',0050,1000)


select month,sum(sales) over (partition by datepart(mm,month)) Sales
from @test111

Output :

month       Sales
2015-05-18  27000
2015-05-19  27000
2015-05-20  27000
2015-05-21  27000
2016-06-01  5000
2016-06-02  5000
Ragesh
  • 740
  • 3
  • 9