Trouble when count by week in SQL

Question

Trouble when count by week in SQL

nhuytan

Newbie

Points: 6
More actions
February 13, 2011 at 3:19 am

#245457

Hi, all
I'm a newbie. I have a problem with count a column by week in SQL.
Description: I have a table named : Money have 2 column named : amount (int), date (datetime).
So i want to count sum of amount by week. Table can have data in many years.
Thanks.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please read and follow the advice given in the first link in my signature on how to provide sufficient information. This will include table def, ready to use sample data, expected result set and, last but not least, what you've tried so far.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

nhuytan Newbie Points: 6 More actions · Answer 2

Table definition:

CREATE TABLE Money

(

ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

Date DATETIME,

Amount INT,

)

Sample data:

INSERT INTO Money

(ID, Date, Amount)

SELECT'1','2/10/2010','12'UNION ALL

SELECT'2','2/11/2010','13'UNION ALL

SELECT'3','2/12/2010','14'UNION ALL

SELECT'4','2/13/2010','15'UNION ALL

SELECT'5','2/14/2010','16'UNION ALL

SELECT'6','2/15/2010','17'UNION ALL

SELECT'7','2/16/2010','18'UNION ALL

SELECT'8','2/17/2010','19'UNION ALL

SELECT'9','2/18/2010','20'UNION ALL

SELECT'10','2/19/2010','21'UNION ALL

SELECT'11','2/20/2010','22'UNION ALL

SELECT'12','2/21/2010','23'UNION ALL

SELECT'13','2/22/2010','24'UNION ALL

SELECT'14','2/23/2010','25'UNION ALL

SELECT'15','2/24/2010','26'UNION ALL

SELECT'16','2/25/2010','27'UNION ALL

SELECT'17','2/26/2010','28'UNION ALL

SELECT'18','2/27/2010','29'UNION ALL

SELECT'19','2/28/2010','30'UNION ALL

SELECT'20','3/1/2010','31'UNION ALL

SELECT'21','3/2/2010','32'UNION ALL

SELECT'22','3/3/2010','33'UNION ALL

SELECT'23','3/4/2010','34'UNION ALL

SELECT'24','3/5/2010','35'UNION ALL

SELECT'25','3/6/2010','36'UNION ALL

SELECT'26','3/7/2010','37'UNION ALL

SELECT'27','3/8/2010','38'UNION ALL

SELECT'28','3/9/2010','39'UNION ALL

SELECT'29','3/10/2010','40'UNION ALL

SELECT'30','3/11/2010','41'UNION ALL

SELECT'31','3/12/2010','42'UNION ALL

SELECT'32','3/13/2010','43'UNION ALL

SELECT'33','3/14/2010','44'UNION ALL

SELECT'34','3/15/2010','45'UNION ALL

SELECT'35','3/16/2010','46'UNION ALL

SELECT'36','3/17/2010','47'UNION ALL

SELECT'37','3/18/2010','48'UNION ALL

SELECT'38','3/19/2010','49'UNION ALL

SELECT'39','3/20/2010','50'UNION ALL

SELECT'40','3/21/2010','51'UNION ALL

SELECT'41','3/22/2010','52'UNION ALL

SELECT'42','3/23/2010','53'UNION ALL

SELECT'43','3/24/2010','54'UNION ALL

SELECT'44','3/25/2010','55'UNION ALL

SELECT'45','3/26/2010','56'UNION ALL

SELECT'46','3/27/2010','57'UNION ALL

SELECT'47','3/28/2010','58'UNION ALL

SELECT'48','3/29/2010','59'UNION ALL

SELECT'49','3/30/2010','60'UNION ALL

SELECT'50','3/31/2010','61'UNION ALL

SELECT'51','4/1/2010','62'UNION ALL

SELECT'52','4/2/2010','63'UNION ALL

SELECT'53','4/3/2010','64'UNION ALL

SELECT'54','4/4/2010','65'UNION ALL

SELECT'55','4/5/2010','66'UNION ALL

SELECT'56','4/6/2010','67'UNION ALL

SELECT'57','4/7/2010','68'UNION ALL

SELECT'58','4/8/2010','69'UNION ALL

SELECT'59','4/9/2010','70'UNION ALL

SELECT'60','4/10/2010','71'UNION ALL

SELECT'61','4/11/2010','72'UNION ALL

SELECT'62','4/12/2010','73'UNION ALL

SELECT'63','4/13/2010','74'UNION ALL

SELECT'64','4/14/2010','75'UNION ALL

SELECT'65','4/15/2010','76'UNION ALL

SELECT'66','4/16/2010','77'UNION ALL

SELECT'67','4/17/2010','78'UNION ALL

SELECT'68','4/18/2010','79'UNION ALL

SELECT'69','4/19/2010','80'UNION ALL

SELECT'70','4/20/2010','81'UNION ALL

SELECT'71','4/21/2010','82'UNION ALL

SELECT'72','4/22/2010','83'UNION ALL

SELECT'73','4/23/2010','84'UNION ALL

SELECT'74','4/24/2010','85'UNION ALL

SELECT'75','4/25/2010','86'UNION ALL

SELECT'76','4/26/2010','87'UNION ALL

SELECT'77','4/27/2010','88'UNION ALL

SELECT'78','4/28/2010','89'UNION ALL

SELECT'79','4/29/2010','90'UNION ALL

SELECT'80','4/30/2010','91'UNION ALL

SELECT'81','5/1/2010','92'UNION ALL

SELECT'82','5/2/2010','93'UNION ALL

SELECT'83','5/3/2010','94'UNION ALL

SELECT'84','5/4/2010','95'UNION ALL

SELECT'85','5/5/2010','96'UNION ALL

SELECT'86','5/6/2010','97'UNION ALL

SELECT'87','5/7/2010','98'UNION ALL

SELECT'88','5/8/2010','99'UNION ALL

SELECT'89','5/9/2010','100'UNION ALL

SELECT'90','5/10/2010','101'UNION ALL

SELECT'91','5/11/2010','102'UNION ALL

SELECT'92','5/12/2010','103'UNION ALL

SELECT'93','5/13/2010','104'UNION ALL

SELECT'94','5/14/2010','105'UNION ALL

SELECT'95','5/15/2010','106'UNION ALL

SELECT'96','5/16/2010','107'UNION ALL

SELECT'97','5/17/2010','108'UNION ALL

SELECT'98','5/18/2010','109'UNION ALL

SELECT'99','5/19/2010','110'UNION ALL

SELECT'100','5/20/2010','111'UNION ALL

SELECT'101','5/21/2010','112'UNION ALL

SELECT'102','5/22/2010','113'UNION ALL

SELECT'103','5/23/2010','114'UNION ALL

SELECT'104','5/24/2010','115'UNION ALL

SELECT'105','5/25/2010','116'UNION ALL

SELECT'106','5/26/2010','117'UNION ALL

SELECT'107','5/27/2010','118'UNION ALL

SELECT'108','5/28/2010','119'UNION ALL

SELECT'109','5/29/2010','120'UNION ALL

SELECT'110','5/30/2010','121'UNION ALL

SELECT'111','5/31/2010','122'UNION ALL

SELECT'112','6/1/2010','123'UNION ALL

SELECT'113','6/2/2010','124'UNION ALL

SELECT'114','6/3/2010','125'UNION ALL

SELECT'115','6/4/2010','126'UNION ALL

SELECT'116','6/5/2010','127'UNION ALL

SELECT'117','6/6/2010','128'UNION ALL

SELECT'118','6/7/2010','129'UNION ALL

SELECT'119','6/8/2010','130'UNION ALL

SELECT'120','6/9/2010','131'UNION ALL

SELECT'121','6/10/2010','132'UNION ALL

SELECT'122','6/11/2010','133'UNION ALL

SELECT'123','6/12/2010','134'UNION ALL

SELECT'124','6/13/2010','135'UNION ALL

SELECT'125','6/14/2010','136'UNION ALL

SELECT'126','6/15/2010','137'UNION ALL

SELECT'127','6/16/2010','138'UNION ALL

SELECT'128','6/17/2010','139'UNION ALL

SELECT'129','6/18/2010','140'UNION ALL

SELECT'130','6/19/2010','141'UNION ALL

SELECT'131','6/20/2010','142'UNION ALL

SELECT'132','6/21/2010','143'UNION ALL

SELECT'133','6/22/2010','144'UNION ALL

SELECT'134','6/23/2010','145'UNION ALL

SELECT'135','6/24/2010','146'UNION ALL

SELECT'136','6/25/2010','147'UNION ALL

SELECT'137','6/26/2010','148'UNION ALL

SELECT'138','6/27/2010','149'UNION ALL

SELECT'139','6/28/2010','150'UNION ALL

SELECT'140','6/29/2010','151'UNION ALL

SELECT'141','6/30/2010','152'UNION ALL

SELECT'142','7/1/2010','153'UNION ALL

SELECT'143','7/2/2010','154'UNION ALL

SELECT'144','7/3/2010','155'UNION ALL

SELECT'145','7/4/2010','156'UNION ALL

SELECT'146','7/5/2010','157'UNION ALL

SELECT'147','7/6/2010','158'UNION ALL

SELECT'148','7/7/2010','159'UNION ALL

SELECT'149','7/8/2010','160'UNION ALL

SELECT'150','7/9/2010','161'UNION ALL

SELECT'151','7/10/2010','162'UNION ALL

SELECT'152','7/11/2010','163'UNION ALL

SELECT'153','7/12/2010','164'UNION ALL

SELECT'154','7/13/2010','165'UNION ALL

SELECT'155','7/14/2010','166'UNION ALL

SELECT'156','7/15/2010','167'UNION ALL

SELECT'157','7/16/2010','168'UNION ALL

SELECT'158','7/17/2010','169'UNION ALL

SELECT'159','7/18/2010','170'UNION ALL

SELECT'160','7/19/2010','171'UNION ALL

SELECT'161','7/20/2010','172'UNION ALL

SELECT'162','7/21/2010','173'UNION ALL

SELECT'163','7/22/2010','174'UNION ALL

SELECT'164','7/23/2010','175'UNION ALL

SELECT'165','7/24/2010','176'UNION ALL

SELECT'166','7/25/2010','177'UNION ALL

SELECT'167','7/26/2010','178'UNION ALL

SELECT'168','7/27/2010','179'UNION ALL

SELECT'169','7/28/2010','180'UNION ALL

SELECT'170','7/29/2010','181'UNION ALL

SELECT'171','7/30/2010','182'UNION ALL

SELECT'172','7/31/2010','183'UNION ALL

SELECT'173','8/1/2010','184'UNION ALL

SELECT'174','8/2/2010','185'UNION ALL

SELECT'175','8/3/2010','186'UNION ALL

SELECT'176','8/4/2010','187'UNION ALL

SELECT'177','8/5/2010','188'UNION ALL

SELECT'178','8/6/2010','189'UNION ALL

SELECT'179','8/7/2010','190'UNION ALL

SELECT'180','8/8/2010','191'UNION ALL

SELECT'181','8/9/2010','192'UNION ALL

SELECT'182','8/10/2010','193'UNION ALL

SELECT'183','8/11/2010','194'UNION ALL

SELECT'184','8/12/2010','195'UNION ALL

SELECT'185','8/13/2010','196'UNION ALL

SELECT'186','8/14/2010','197'UNION ALL

SELECT'187','8/15/2010','198'UNION ALL

SELECT'188','8/16/2010','199'UNION ALL

SELECT'189','8/17/2010','200'UNION ALL

SELECT'190','8/18/2010','201'UNION ALL

SELECT'191','8/19/2010','202'UNION ALL

SELECT'192','8/20/2010','203'UNION ALL

SELECT'193','8/21/2010','204'UNION ALL

SELECT'194','8/22/2010','205'UNION ALL

SELECT'195','8/23/2010','206'UNION ALL

SELECT'196','8/24/2010','207'UNION ALL

SELECT'197','8/25/2010','208'UNION ALL

SELECT'198','8/26/2010','209'UNION ALL

SELECT'199','8/27/2010','210'UNION ALL

SELECT'200','8/28/2010','211'UNION ALL

SELECT'201','8/29/2010','212'UNION ALL

SELECT'202','8/30/2010','213'UNION ALL

SELECT'203','8/31/2010','214'UNION ALL

SELECT'204','9/1/2010','215'UNION ALL

SELECT'205','9/2/2010','216'UNION ALL

SELECT'206','9/3/2010','217'UNION ALL

SELECT'207','9/4/2010','218'UNION ALL

SELECT'208','9/5/2010','219'UNION ALL

SELECT'209','9/6/2010','220'UNION ALL

SELECT'210','9/7/2010','221'UNION ALL

SELECT'211','9/8/2010','222'UNION ALL

SELECT'212','9/9/2010','223'UNION ALL

SELECT'213','9/10/2010','224'UNION ALL

SELECT'214','9/11/2010','225'UNION ALL

SELECT'215','9/12/2010','226'UNION ALL

SELECT'216','9/13/2010','227'UNION ALL

SELECT'217','9/14/2010','228'UNION ALL

SELECT'218','9/15/2010','229'UNION ALL

SELECT'219','9/16/2010','230'UNION ALL

SELECT'220','9/17/2010','231'UNION ALL

SELECT'221','9/18/2010','232'UNION ALL

SELECT'222','9/19/2010','233'UNION ALL

SELECT'223','9/20/2010','234'UNION ALL

SELECT'224','9/21/2010','235'UNION ALL

SELECT'225','9/22/2010','236'UNION ALL

SELECT'226','9/23/2010','237'UNION ALL

SELECT'227','9/24/2010','238'UNION ALL

SELECT'228','9/25/2010','239'UNION ALL

SELECT'229','9/26/2010','240'UNION ALL

SELECT'230','9/27/2010','241'UNION ALL

SELECT'231','9/28/2010','242'UNION ALL

SELECT'232','9/29/2010','243'UNION ALL

SELECT'233','9/30/2010','244'UNION ALL

SELECT'234','10/1/2010','245'UNION ALL

SELECT'235','10/2/2010','246'UNION ALL

SELECT'236','10/3/2010','247'UNION ALL

SELECT'237','10/4/2010','248'UNION ALL

SELECT'238','10/5/2010','249'UNION ALL

SELECT'239','10/6/2010','250'UNION ALL

SELECT'240','10/7/2010','251'UNION ALL

SELECT'241','10/8/2010','252'UNION ALL

SELECT'242','10/9/2010','253'UNION ALL

SELECT'243','10/10/2010','254'UNION ALL

SELECT'244','10/11/2010','255'UNION ALL

SELECT'245','10/12/2010','256'UNION ALL

SELECT'246','10/13/2010','257'UNION ALL

SELECT'247','10/14/2010','258'UNION ALL

SELECT'248','10/15/2010','259'UNION ALL

SELECT'249','10/16/2010','260'UNION ALL

SELECT'250','10/17/2010','261'UNION ALL

SELECT'251','10/18/2010','262'UNION ALL

SELECT'252','10/19/2010','263'UNION ALL

SELECT'253','10/20/2010','264'UNION ALL

SELECT'254','10/21/2010','265'UNION ALL

SELECT'255','10/22/2010','266'UNION ALL

SELECT'256','10/23/2010','267'UNION ALL

SELECT'257','10/24/2010','268'UNION ALL

SELECT'258','10/25/2010','269'UNION ALL

SELECT'259','10/26/2010','270'UNION ALL

SELECT'260','10/27/2010','271'UNION ALL

SELECT'261','10/28/2010','272'UNION ALL

SELECT'262','10/29/2010','273'UNION ALL

SELECT'263','10/30/2010','274'UNION ALL

SELECT'264','10/31/2010','275'UNION ALL

SELECT'265','11/1/2010','276'UNION ALL

SELECT'266','11/2/2010','277'UNION ALL

SELECT'267','11/3/2010','278'UNION ALL

SELECT'268','11/4/2010','279'UNION ALL

SELECT'269','11/5/2010','280'UNION ALL

SELECT'270','11/6/2010','281'UNION ALL

SELECT'271','11/7/2010','282'UNION ALL

SELECT'272','11/8/2010','283'UNION ALL

SELECT'273','11/9/2010','284'UNION ALL

SELECT'274','11/10/2010','285'UNION ALL

SELECT'275','11/11/2010','286'UNION ALL

SELECT'276','11/12/2010','287'UNION ALL

SELECT'277','11/13/2010','288'UNION ALL

SELECT'278','11/14/2010','289'UNION ALL

SELECT'279','11/15/2010','290'UNION ALL

SELECT'280','11/16/2010','291'UNION ALL

SELECT'281','11/17/2010','292'UNION ALL

SELECT'282','11/18/2010','293'UNION ALL

SELECT'283','11/19/2010','294'UNION ALL

SELECT'284','11/20/2010','295'UNION ALL

SELECT'285','11/21/2010','296'UNION ALL

SELECT'286','11/22/2010','297'UNION ALL

SELECT'287','11/23/2010','298'UNION ALL

SELECT'288','11/24/2010','299'UNION ALL

SELECT'289','11/25/2010','300'UNION ALL

SELECT'290','11/26/2010','301'UNION ALL

SELECT'291','11/27/2010','302'UNION ALL

SELECT'292','11/28/2010','303'UNION ALL

SELECT'293','11/29/2010','304'UNION ALL

SELECT'294','11/30/2010','305'UNION ALL

SELECT'295','12/1/2010','306'UNION ALL

SELECT'296','12/2/2010','307'UNION ALL

SELECT'297','12/3/2010','308'UNION ALL

SELECT'298','12/4/2010','309'UNION ALL

SELECT'299','12/5/2010','310'UNION ALL

SELECT'300','12/6/2010','311'UNION ALL

SELECT'301','12/7/2010','312'UNION ALL

SELECT'302','12/8/2010','313'UNION ALL

SELECT'303','12/9/2010','314'UNION ALL

SELECT'304','12/10/2010','315'UNION ALL

SELECT'305','12/11/2010','316'UNION ALL

SELECT'306','12/12/2010','317'UNION ALL

SELECT'307','12/13/2010','318'UNION ALL

SELECT'308','12/14/2010','319'UNION ALL

SELECT'309','12/15/2010','320'UNION ALL

SELECT'310','12/16/2010','321'UNION ALL

SELECT'311','12/17/2010','322'UNION ALL

SELECT'312','12/18/2010','323'UNION ALL

SELECT'313','12/19/2010','324'UNION ALL

SELECT'314','12/20/2010','325'UNION ALL

SELECT'315','12/21/2010','326'UNION ALL

SELECT'316','12/22/2010','327'UNION ALL

SELECT'317','12/23/2010','328'UNION ALL

SELECT'318','12/24/2010','329'UNION ALL

SELECT'319','12/25/2010','330'UNION ALL

SELECT'320','12/26/2010','331'UNION ALL

SELECT'321','12/27/2010','332'UNION ALL

SELECT'322','12/28/2010','333'UNION ALL

SELECT'323','12/29/2010','334'UNION ALL

SELECT'324','12/30/2010','335'UNION ALL

SELECT'325','12/31/2010','336'UNION ALL

SELECT'326','1/1/2011','337'UNION ALL

SELECT'327','1/2/2011','338'UNION ALL

SELECT'328','1/3/2011','339'UNION ALL

SELECT'329','1/4/2011','340'UNION ALL

SELECT'330','1/5/2011','341'UNION ALL

SELECT'331','1/6/2011','342'UNION ALL

SELECT'332','1/7/2011','343'UNION ALL

SELECT'333','1/8/2011','344'UNION ALL

SELECT'334','1/9/2011','345'UNION ALL

SELECT'335','1/10/2011','346'UNION ALL

SELECT'336','1/11/2011','347'UNION ALL

SELECT'337','1/12/2011','348'UNION ALL

SELECT'338','1/13/2011','349'UNION ALL

SELECT'339','1/14/2011','350'UNION ALL

SELECT'340','1/15/2011','351'UNION ALL

SELECT'341','1/16/2011','352'UNION ALL

SELECT'342','1/17/2011','353'UNION ALL

SELECT'343','1/18/2011','354'UNION ALL

SELECT'344','1/19/2011','355'UNION ALL

SELECT'345','1/20/2011','356'UNION ALL

SELECT'346','1/21/2011','357'UNION ALL

SELECT'347','1/22/2011','358'UNION ALL

SELECT'348','1/23/2011','359'UNION ALL

SELECT'349','1/24/2011','360'UNION ALL

SELECT'350','1/25/2011','361'UNION ALL

SELECT'351','1/26/2011','362'UNION ALL

SELECT'352','1/27/2011','363'UNION ALL

SELECT'353','1/28/2011','364'UNION ALL

SELECT'354','1/29/2011','365'UNION ALL

SELECT'355','1/30/2011','366'UNION ALL

SELECT'356','1/31/2011','367'UNION ALL

SELECT'357','2/1/2011','368'UNION ALL

SELECT'358','2/2/2011','369'UNION ALL

SELECT'359','2/3/2011','370'UNION ALL

SELECT'360','2/4/2011','371'UNION ALL

SELECT'361','2/5/2011','372'UNION ALL

SELECT'362','2/6/2011','373'UNION ALL

SELECT'363','2/7/2011','374'UNION ALL

SELECT'364','2/8/2011','375'UNION ALL

SELECT'365','2/9/2011','376'UNION ALL

SELECT'366','2/10/2011','377'UNION ALL

SELECT'367','2/11/2011','378'UNION ALL

SELECT'368','2/12/2011','379'UNION ALL

SELECT'369','2/13/2011','380'UNION ALL

SELECT'370','2/14/2011','381'UNION ALL

SELECT'371','2/15/2011','382'UNION ALL

SELECT'372','2/16/2011','383'UNION ALL

SELECT'373','2/17/2011','384'UNION ALL

SELECT'374','2/18/2011','385'UNION ALL

SELECT'375','2/19/2011','386'UNION ALL

SELECT'376','2/20/2011','387'UNION ALL

SELECT'377','2/21/2011','388'UNION ALL

SELECT'378','2/22/2011','389'UNION ALL

SELECT'379','2/23/2011','390'UNION ALL

SELECT'380','2/24/2011','391'UNION ALL

SELECT'381','2/25/2011','392'UNION ALL

SELECT'382','2/26/2011','393'UNION ALL

SELECT'383','2/27/2011','394'UNION ALL

SELECT'384','2/28/2011','395'UNION ALL

SELECT'385','3/1/2011','396'UNION ALL

SELECT'386','3/2/2011','397'UNION ALL

SELECT'387','3/3/2011','398'UNION ALL

SELECT'388','3/4/2011','399'UNION ALL

SELECT'389','3/5/2011','400'UNION ALL

SELECT'390','3/6/2011','401' )

Expected result : count sum amount by week.

Week sum

W06-2010 70

W07-2010 140

W08-2010 189

W09-2010 238

W10-2010 287

.

W09-2011 2786

Thanks for your comment.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

This will do the necessary summation and some output formatting.

SELECT 'W'+ CAST(DATEPART(wk,[Date]) AS VARCHAR(2))+ '-'

+ CAST(DATEPART(yy,[Date]) AS VARCHAR(4)),SUM(Amount) AS Amount

FROM #Money GROUP BY DATEPART(wk,[Date]),DATEPART(yy,[Date])

ORDER BY DATEPART(yy,[Date]),DATEPART(wk,[Date])

Typical output:

W7-201054

W8-2010133

W9-2010182

W10-2010 231

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

Nicely done, Ron. It's amazing what a little provided data can do.

As a side bar, we still need to know what the OP's definition of "a week" is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)