Hey,

I have a table that records all my bank transactions.  So in it there
is one entry per transaction.  This entry has the date the transaction
occured.

All I want to do is perform a query that returns how many transactions
I had for each month.

Ie.

jan : 24 transactions
feb : 19 transactions
etc

is this possible?  I know I can do "counts" ... but it seems hard
because I need to split the results return by the month it occurs in.

Any help you can give would be greatly appreciated!!

Thanx

Ryan Ritten

select Year(Date) As Year ,month(Date) Month, count(*) as TransCount from
YourTable
group by Year(Date),month(Date)

Not the most efficient way, but...

SELECT YEAR(TransactionDate), MONTH(TransactionDate), COUNT(*)
    FROM TransactionTable
    -- if you want to limit to specific year:
    -- WHERE TransactionDate >= '20050101' AND TransactionDate < '20060101'
    GROUP BY YEAR(TransactionDate), MONTH(TransactionDate)
    ORDER BY 1,2




Show quoteHide quote

Sparticus,

This should give you what you want.  You'll need to WHERE for a specify year
or year range.  If you want month names and/or you want the months with 0 or
as columns instead of rows...let me know.

SELECT DATEPART(MM,TDATE)AS 'MONTH', COUNT(*) 'TRANSACTION COUNT'
FROM #TRANSACTIONS
GROUP BY DATEPART(MM,TDATE)

HTH

Jerry
Show quoteHide quote

wow...thanx everyone.  Learn somethign new everyday :)

group your data by datepart(month,datefield),datepart(year,datefield)

http://sqlservercode.blogspot.com/


Show quoteHide quote

CREATE TABLE #MyTransaction
(
id int NOT NULL IDENTITY (1, 1),
[date] datetime NULL,
amount money NULL
)  ON [PRIMARY]

insert into #MyTransaction
([date], amount)
values
('1-1-05', 100)
insert into #MyTransaction
([date], amount)
values
('1-10-05', 100)
insert into #MyTransaction
([date], amount)
values
('1-20-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-1-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-5-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-7-05', 100)

insert into #MyTransaction
([date], amount)
values
('3-8-05', 10)


select datepart(yy,[date]) as Year, datename(mm,[date]) as month, count(id)
as Count
from #MyTransaction
group by datepart(yy,[date]), datename(mm,[date])

drop table #MyTransaction

Show quoteHide quote

Who Added Indexing to Table?
sql weird
NOLOCK
Retrieval of IDENTITY after "bulk" insert?
REPLACE query help plz!