sum, group by 3 - 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, group by 3

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2004-09-01 : 18:16:15

I have a table that has a period number and a sales dollar in it. I need to summary the dollars, grouping the periods by sets of 3. The period numbers are sequential and change/increment each month (hopefully a number will never be skipped). The most recent period numer is 408, so 408,407,406 need to be summarized together, 405,404,403 together etc... Does anyone know how I can do this? Thanks in advance.

CREATE TABLE Sales
(Period varchar(3), Dollars smallmoney)
INSERT Sales values ('408', 100.00)
INSERT Sales values ('408', 50.00)
INSERT Sales values ('407', 30.00)
INSERT Sales values ('407', 20.00)
INSERT Sales values ('406', 10.00)
INSERT Sales values ('405', 11.00)
INSERT Sales values ('404', 13.00)
INSERT Sales values ('404', 12.00)
INSERT Sales values ('403', 12.00)
INSERT Sales values ('402', 84.00)
INSERT Sales values ('402', 182.00)

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-01 : 19:04:24

What should be final results.

mk_garg

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-01 : 19:07:06

This should do it...


Declare @Sales TABLE (Period varchar(3), Dollars smallmoney)
INSERT @Sales values ('408', 100.00)
INSERT @Sales values ('408', 50.00)
INSERT @Sales values ('407', 30.00)
INSERT @Sales values ('407', 20.00)
INSERT @Sales values ('406', 10.00)
INSERT @Sales values ('405', 11.00)
INSERT @Sales values ('404', 13.00)
INSERT @Sales values ('404', 12.00)
INSERT @Sales values ('403', 12.00)
INSERT @Sales values ('402', 84.00)
INSERT @Sales values ('402', 182.00)


Select bPeriod, ePeriod, Dollars=sum(Dollars)
From
	(
	Select 
		bPeriod = case when Period%3=0 then (Period/3-1)*3+1 else (Period/3)*3+1 end, 
		ePeriod = case when Period%3=0 then (Period/3)*3 else (Period/3+1)*3 end, 
		Period,
		Dollars 
	From @Sales
	) as A
Group By bPeriod, ePeriod

Corey

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-01 : 19:18:45

cool!

mk_garg

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-09-01 : 19:19:16

[edit]

by 12 minutes!
Nice job Corey. I like your results better than mine.
[/edit]

I almost gave up on this, but I figured it out!


CREATE TABLE #Sales(Period varchar(3), Dollars smallmoney)
CREATE TABLE #Sales2(Period varchar(3), Dollars smallmoney, SumGroup INT)


INSERT #Sales values ('408', 100.00)
INSERT #Sales values ('408', 50.00)
INSERT #Sales values ('407', 30.00)
INSERT #Sales values ('407', 20.00)
INSERT #Sales values ('406', 10.00)
INSERT #Sales values ('405', 11.00)
INSERT #Sales values ('404', 13.00)
INSERT #Sales values ('404', 12.00)
INSERT #Sales values ('403', 12.00)
INSERT #Sales values ('402', 84.00)
INSERT #Sales values ('402', 182.00)

INSERT INTO #Sales2(Period, Dollars, SumGroup)
SELECT Period, Dollars, 0
FROM #Sales


/*
SELECT Period, SumGroup 
FROM #Sales2 
GROUP BY Period, SumGroup 
HAVING Period = (SELECT MAX(period) AS TheMaxPeriod FROM #Sales2)
*/
WHILE (SELECT COUNT(*) FROM #Sales2 WHERE SumGroup = 0) > 0 
	BEGIN
		UPDATE #Sales2 SET
		SumGroup = a.MaxSumGroup + 1
		FROM (
			SELECT MAX(SumGroup) AS MaxSumGroup FROM #Sales2
		      ) A
		WHERE #Sales2.Period IN (
			SELECT TOP 3 Period
			FROM #Sales2
			GROUP BY Period, SumGroup
			HAVING SumGroup = 0 
			ORDER BY Period DESC
		)
	END

SELECT SUM(Dollars)
FROM #Sales2
GROUP BY SumGroup


--SELECT MAX(period) FROM #Sales2

--select * from #sales2


DROP TABLE #Sales2
DROP TABLE #Sales