SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to calculate SUM for this?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/18/2013 :  16:27:02  Show Profile  Reply with Quote
this is my Table1

ID________BUYER_______DATE_____________BILL

1_________John________14.03.2013_______3000
2_________Bob_________11.02.2013_______2700
3_________John________05.02.2013_______2000
4_________Adam________18.01.2013_______1000
5_________Bob_________15.02.2013_______500
6_________John________19.02.2013_______800
7_________Adam________22.03.2013_______200
8_________John________27.01.2013_______1200

I want to calculate sum of BILL'S for each buyers monthly (in this example i want to calculate sum for John, Bob and Adam for january, february and march) and sum for all months by users...

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/18/2013 :  17:01:15  Show Profile  Reply with Quote
What do you want the output to be?

Also, can you put your data in a consumable format so we can run queries against it? Here are some links to help you with that if you are not sure how:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/18/2013 :  17:21:18  Show Profile  Reply with Quote
I want my results from Table1 to be like this

JohnJanuary (sum of all John's bill's in january)
JohnFebruary (sum of all John's bill's in February)
JohnMarch (sum of all John's bill's in march)
JohnTotak (sum of all John's Bill's)
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/18/2013 :  17:47:03  Show Profile  Reply with Quote
You may want to try this...

CREATE TABLE dbo.CustomerData(
ID Int,
Buyer NVARCHAR(10),
bdate Date,
bill MONEY);


INSERT INTO dbo.CustomerData(ID, Buyer, bdate, bill) Values(1, 'John', '20130314', 3000),
(2, 'Bob', '20130211', 2700),
(3, 'John', '20130205', 2000),
(4, 'Adam', '20130118', 1000),
(5, 'Bob', '20130215', 500),
(6, 'John', '20130219', 800),
(7, 'Adam', '20130322', 200),
(8, 'John', '20130127', 1200);


Select  Buyer, Month(bdate) as BMonth, Year(bdate) as BYear, Sum(bill) AS TOTAL  
from dbo.CustomerData group by Rollup(Buyer, Year(bdate), Month(bdate));


OR this if you dont want totals for each year

Select  Buyer, Month(bdate) as BMonth, Sum(bill) AS TOTAL  
from dbo.CustomerData group by Rollup(Buyer, Month(bdate));


Edited by - MuMu88 on 04/18/2013 18:08:02
Go to Top of Page

joe8079
Posting Yak Master

USA
127 Posts

Posted - 04/18/2013 :  19:04:12  Show Profile  Reply with Quote
CREATE TABLE dbo.CustomerData(
ID Int,
Buyer NVARCHAR(10),
bdate Date,
bill MONEY);


INSERT INTO dbo.CustomerData(ID, Buyer, bdate, bill) Values(1, 'John', '20130314', 3000),
(2, 'Bob', '20130211', 2700),
(3, 'John', '20130205', 2000),
(4, 'Adam', '20130118', 1000),
(5, 'Bob', '20130215', 500),
(6, 'John', '20130219', 800),
(7, 'Adam', '20130322', 200),
(8, 'John', '20130127', 1200);



or you could try:
this should give totals for ever person and the month.


select x.buyer,month(x.bdate)as month,year(x.bdate) bYear, q.totalbymonth

from dbo.CustomerData x cross apply (select sum(y.bill) totalbymonth from dbo.CustomerData y where x.buyer = y.buyer and month(x.bdate) = month(y.bdate) and year(x.bdate) = year(y.bdate)) q
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/20/2013 :  12:09:30  Show Profile  Reply with Quote
I try this code from JOE8079 but that is not i lookig for.

I want my resauls to be like this.

columns(ID,BUYER,MonthJanuary,MonthFebruary,MonthMarch, Total)
(1,'John',1200,2800,3000,7000)
(2,'Bob',null,3200,null,3200)
(3,'Adam',1000,null,200,1200)

In my real table i have over 2000 rows

Thanks guys!!!

Edited by - nikoz on 04/20/2013 12:39:04
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 04/20/2013 :  13:01:34  Show Profile  Reply with Quote
Use Mumu's query and PIVOT it like shown below. It may be a little shorter if you simply added up the 12 months to calculate the total rather than use the grouping sets etc.
select Row_number() over (order by Buyer) as Id,Buyer,[1] as Jan, [2] as Feb, [3] as Mar, [13] as Total from (
select Buyer, coalesce(BMonth,'13') as Bmonth,Total from (
Select  Buyer, Month(bdate) as BMonth, Year(bdate) as BYear, Sum(bill) AS TOTAL  
from dbo.CustomerData group by grouping sets ((Buyer, Year(bdate), Month(bdate)),(Buyer))) x)
s pivot (SUM(Total) for Bmonth in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13]))p
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 04/20/2013 :  13:04:55  Show Profile  Reply with Quote
This is Mumu's query with rollup removed and then pivoted
select Row_number() over (order by Buyer) as Id,Buyer,[1] as Jan, [2] as Feb, [3] as Mar, 
isnull([1],0)+isnull([2],0)+isnull([3],0) as Total from (
Select  Buyer, Month(bdate) as BMonth, Year(bdate) as BYear, Sum(bill) AS TOTAL  
from dbo.CustomerData group by Buyer, Year(bdate), Month(bdate) )
s pivot (SUM(Total) for Bmonth in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13]))p
By the way, if your results span more than one year, what do you want to do? You don't want to aggregate data from multiple years of January into one value, do you?

Edited by - James K on 04/20/2013 13:06:05
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/20/2013 :  13:21:45  Show Profile  Reply with Quote
I will try to explain like this.
In table dbo.CostumerData I want to know SUM how much for each buyers are by in January, February and March in year 2013 and TOTAL (January + February + March)...

and i have over 2000 buyers...

Edited by - nikoz on 04/20/2013 13:26:33
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/20/2013 :  13:26:08  Show Profile  Reply with Quote
For example John sum for Janury is 1200,
for febrary is 2800 because he bought twice in Februaru
for march is 3000
and total is 7000 (January + February + March)
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 04/20/2013 :  15:09:35  Show Profile  Reply with Quote
Nikoz, that is what both the queries I posted are supposed to do. Can you run the query against your table and see what it returns, and if it is not correct, post what it is giving, and what you are expecting?
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/21/2013 :  09:40:46  Show Profile  Reply with Quote
Second query is good. But i have a lot of unnecessary NULL's.
My resault look like this

(1,'John','NULL','NULL','NULL','NULL')
(2,'John','NULL','NULL','NULL','NULL')
(3,'John',1200,2800,3000,7000)

Is it posible to remove that unnecessary NULL's ?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 04/21/2013 :  18:06:48  Show Profile  Reply with Quote
quote:
Originally posted by nikoz

Second query is good. But i have a lot of unnecessary NULL's.
My resault look like this

(1,'John','NULL','NULL','NULL','NULL')
(2,'John','NULL','NULL','NULL','NULL')
(3,'John',1200,2800,3000,7000)

Is it posible to remove that unnecessary NULL's ?

Do you have additional columns or some other artifact? I am using MuMu's test data that she posted at 04/18/2013 : 17:47:03. Then, I am using the query I had posted earlier. The query and the results I get are shown below. Is this not what you are getting?
select Row_number() over (order by Buyer) as Id,Buyer,[1] as Jan, [2] as Feb, [3] as Mar, 
isnull([1],0)+isnull([2],0)+isnull([3],0) as Total from (
Select  Buyer, Month(bdate) as BMonth, Year(bdate) as BYear, Sum(bill) AS TOTAL  
from dbo.CustomerData group by Buyer, Year(bdate), Month(bdate) )
s pivot (SUM(Total) for Bmonth in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13]))p

-- RESULTS-------------------------------------------
Id	Buyer	Jan	Feb	Mar	Total
1	Adam	1000.00	NULL	200.00	1200.00
2	Bob	NULL	3200.00	NULL	3200.00
3	John	1200.00	2800.00	3000.00	7000.00

Edited by - James K on 04/21/2013 18:07:27
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/22/2013 :  10:09:08  Show Profile  Reply with Quote
Thanks guys!!! Resault is ok.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000