sum dollars for periods - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

sum dollars for periods

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2003-09-16 : 13:34:08

I have a table that has a YYYYMM date field and a dollar field. I need to return the data in 3 month chunks, with a summary, how can I do this without using a cursor. This is what i need:

Date Amount
200301 100
200302 200
200303 50
Total 350
200304 60
200305 100
200306 80
Total 240
Grand 590

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-16 : 13:36:26

Please see the information in the following thread which shows you what information that we need in order to be able to answer your question:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29090[/url]

Tara

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2003-09-16 : 17:48:19

Table Creation:
CREATE TABLE [dbo].[sales] (
[rec_id] [int] IDENTITY (1, 1) NOT NULL ,
[sales_date] [char] (6) NULL ,
[sales_amount] [int] NULL
) ON [PRIMARY]
GO

Data:
insert sales (sales_date, sales_amount) values ('200301',50)
insert sales (sales_date, sales_amount) values ('200301',50)
insert sales (sales_date, sales_amount) values ('200302',150)
insert sales (sales_date, sales_amount) values ('200302',50)
insert sales (sales_date, sales_amount) values ('200303',50)
insert sales (sales_date, sales_amount) values ('200304',60)
insert sales (sales_date, sales_amount) values ('200305',75)
insert sales (sales_date, sales_amount) values ('200305',25)
insert sales (sales_date, sales_amount) values ('200306',80)

Desired results are a total per date, as well as a total per every 3rd month grouping, and final total:

Date Amount
200301 100
200302 200
200303 50
Total 350
200304 60
200305 100
200306 80
Total 240
Grand 590

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-16 : 18:09:57

I couldn't figure out how to do it in one statement, but maybe someone else can. Also, sales_date isn't a valid date for SQL Server. It would be much easier if you stored the values as valid dates.

Anyway, here ya go:


SET NOCOUNT ON

CREATE TABLE #Temp
([Date] CHAR(6) NOT NULL, Amount INT NOT NULL)

CREATE TABLE [dbo].[sales] (
[rec_id] [int] IDENTITY (1, 1) NOT NULL ,
[sales_date] [char] (6) NULL ,
[sales_amount] [int] NULL 
) ON [PRIMARY]
GO

insert sales (sales_date, sales_amount) values ('200301',50)
insert sales (sales_date, sales_amount) values ('200301',50)
insert sales (sales_date, sales_amount) values ('200302',150)
insert sales (sales_date, sales_amount) values ('200302',50)
insert sales (sales_date, sales_amount) values ('200303',50)
insert sales (sales_date, sales_amount) values ('200304',60)
insert sales (sales_date, sales_amount) values ('200305',75)
insert sales (sales_date, sales_amount) values ('200305',25)
insert sales (sales_date, sales_amount) values ('200306',80)

INSERT INTO #Temp ([Date], Amount)
SELECT sales_date, SUM(sales_amount)
FROM sales
WHERE DATEPART(year, CONVERT(VARCHAR(50), sales_date + '01', 120)) = '2003' AND
DATEPART(month, CONVERT(VARCHAR(50), sales_date + '01', 120)) IN ('01', '02', '03') 
GROUP BY sales_date

INSERT INTO #Temp ([Date], Amount)
SELECT 'Total', SUM(sales_amount)
FROM sales
WHERE DATEPART(year, CONVERT(VARCHAR(50), sales_date + '01', 120)) = '2003' AND
DATEPART(month, CONVERT(VARCHAR(50), sales_date + '01', 120)) IN ('01', '02', '03') 

INSERT INTO #Temp ([Date], Amount)
SELECT sales_date, SUM(sales_amount)
FROM sales
WHERE DATEPART(year, CONVERT(VARCHAR(50), sales_date + '01', 120)) = '2003' AND
DATEPART(month, CONVERT(VARCHAR(50), sales_date + '01', 120)) IN ('04', '05', '06') 
GROUP BY sales_date

INSERT INTO #Temp ([Date], Amount)
SELECT 'Total', SUM(sales_amount)
FROM sales
WHERE DATEPART(year, CONVERT(VARCHAR(50), sales_date + '01', 120)) = '2003' AND
DATEPART(month, CONVERT(VARCHAR(50), sales_date + '01', 120)) IN ('04', '05', '06') 

INSERT INTO #Temp ([Date], Amount)
SELECT 'Grand', SUM(sales_amount)
FROM sales
WHERE DATEPART(year, CONVERT(VARCHAR(50), sales_date + '01', 120)) = '2003'

SELECT [Date], Amount
FROM #Temp

DROP TABLE sales
DROP TABLE #Temp

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-16 : 18:22:15

subtotaling on groups and and a grand total at the end of your data is a presentation issue. Report writers such as Access and Crystal make this trivial.

You should only be calculating subtotals or grand-totals in SQL if you need them for futher computation WITHIN further SQL statements. Computation or subtotals for the sake of displaying them to the user is a presentation issue.

EDIT: to fix some bad typos!!

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-16 : 18:24:27

Yes, I forgot to mention that.

Tara

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-17 : 05:46:35

I agree 100% with what Jeff has said, this is a presentation layer issue. If you still want to do in SQL Server, the ROLLUP keyword is what you are looking for:


select 
	datepart(qq,cast(sales_date+'01' as datetime)) as sales_quarter,
	cast(sales_date+'01' as datetime) as sales_date, 
	sum(sales_amount)
from sales
group by 
	datepart(qq,cast(sales_date+'01' as datetime)),
	cast(sales_date+'01' as datetime)
with rollup

Make it idiot proof and someone will make a better idiot

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2003-09-17 : 11:52:28

Thanks for the great help, especially Mohdowais.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources