CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3712641
user3712641

Reputation: 149

How to count the number of rows pertaining to a certain year within a table

I have a table (named trxItemdata) which contains over 40 columns and 60million rows. One of these columns, named ActivityDateDate shows the date/year associated with each CustomerID (another column in the table).

What I would like to do is find the number of rows for allocated to each year (2010,2011,etc), such that I get a table that looks like this in the results output:

Year    Number of Rows
2011    100
2012    10000
2013    10000000

I was looking into the following code but am not too familiar with group by clauses:

select count(*) from trxItemdata
group by year(ActivityDateDate)

However when I run this I get the following table but am not sure what it means:

No Column Name
33060000
27546960
2941697

Any help you could provide would be appreciated! Thanks!

Upvotes: 0

Views: 87

Answers (2)

Kritner
Kritner

Reputation: 13765

try

select year(activityDateDate) as [Year], count(1) as [Number Of Rows]
from trxItemdata
group by year(ActivityDateDate)
order by year(ActivityDateDate)

Does your date column really have "DateDate"? :P

Upvotes: 3

DavidG
DavidG

Reputation: 118957

Your query is not naming the column. Try this:

SELECT YEAR(ActivityDateDate) AS [Year],
       COUNT(*) AS NumberOfRows
FROM trxItemdata
GROUP BY YEAR(ActivityDateDate)

Upvotes: 2

Related Questions