Urgent Count by year - 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

General SQL Server Forums

New to SQL Server Programming

Urgent Count by year

Author

Topic

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-16 : 09:47:45

Hi Guys i am doing a task at wor, where I have data with a date set as 19000101, my task is to count how many records there are in each year so for example the data table i have is this one below

sId Row Dam Date Pat Code Age Value
1x 1 xxxxx 20071009 1xx -1 1 28
1x 2 xxxxx 20070904 1xx -1 1 10
1x 3 xxxxx 20000911 1xx -1 1 11
1x 4 xxxxx 19950629 1xx -1 1 1
1x 5 bbbbb 19950209 1xx -1 1 173

so from the above i want to do a count on records per year so result should be

Year Count
2007 2
2000 1
1995 2

does anyone know how to do this?

i would be very greatful for the help

thank you guys

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-16 : 09:53:03

select
Year([Date]) as [Year],
Count(*) as [Count]
from table
group by Year([Date])

No, you're never too old to Yak'n'Roll if you're too young to die.

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-16 : 10:12:15

i cant get this to work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 10:14:04

quote:
Originally posted by w1102157

i cant get this to work

why?
whats the issue?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-16 : 10:34:40

its not pulling all the dates across

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-16 : 10:36:45

quote:
Originally posted by w1102157

its not pulling all the dates across

what do you mean by that ? Example ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 10:48:01

quote:
Originally posted by w1102157

its not pulling all the dates across

show some data from and explain which dates are getting missed out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-16 : 16:40:52

ok i will do this tomaarow when i get to work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 22:37:18

ok...will check it then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-17 : 05:59:51

right i am just doing ti again, i didnt put all the detailsn on my original question sorry guys my fault , i am using two tables so example is shown below, the two tables i am using is the following

tab 1

SID RID Dam DateID Pat code Age VA
1x 1 xxxxx 1111 1xx -1 1 45
1x 2 xxxxx 2222 1xx -1 1 34
1x 3 xxxxx 3333 1xx -1 1 35
1x 4 xxxxx 4444 1xx -1 1 56
1x 5 xxxxz 4444 1xx -1 1 120

tab 2

DateID D M Y
1111 1800-01-01 NA NA
2222 05/01/2009 jan 2009
3333 06/01/2011 jan 2011
4444 07/01/2012 jan 2012

so what i need to do is link the first table to the second table to pull out the date on date id ( or pull out the year to make even more simple) and then i need to doa count on how much records are in each year

so using the above table the output i want should be

Year Count
NA 1
2009 1
2011 1
2012 2

am i making sense?

thank you guys

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-17 : 08:15:34

anyone? pleaseeee

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-17 : 08:22:18

select
t2.Y as [Year],
count(*) as [Count]
from tab1 as t1
join tab2 as t2 on t2.DateID = t1.DateID
group by t2.Y

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources