How to calculate SUM for this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to calculate SUM for this?

Author

Topic

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-04-18 : 16:27:02

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-18 : 17:01:15

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

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-04-18 : 17:21:18

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)

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-18 : 17:47:03

You may want to try this...
[CODE]
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));

[/CODE]

joe8079
Posting Yak Master

127 Posts

Posted - 2013-04-18 : 19:04:12

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

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-04-20 : 12:09:30

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!!!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-20 : 13:01:34

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-20 : 13:04:55

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?

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-04-20 : 13:21:45

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

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-04-20 : 13:26:08

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)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-20 : 15:09:35

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?

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-04-21 : 09:40:46

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 ?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-21 : 18:06:48

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

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-04-22 : 10:09:08

Thanks guys!!! Resault is ok.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources