database - SQL counts of tests given in year -

database - SQL counts of tests given in year -

i'm trying create sum of tests given in year specific table. have far:

select distinct to_char(test_date, 'yyyy') year, sum(yearcount) from( select count(test_date) yearcount test_record ), test_record grouping test_record.test_date order year asc;

which gives me output:

year sum(yearcount) ---- -------------- 1958 12 1991 12 1996 12 1998 12 2000 12 2001 12 2010 12 2012 12 2013 12

now, understand problem lies here: select count(test_date) yearcount , because have 12 entries in table it's giving count of number of entries in table. need count of tests given in each year, i.e. output should this:

year sum(yearcount) ---- -------------- 1958 1 1991 1 1996 1 1998 1 2000 1 2001 1 2010 1 2012 1 2013 4

so question boils downwards to: how count year in date column? (i'm using oracle 7 believe)

edit: below help able desired output, both little "wrong", didn't take them (sorry if that's faux pas). here script:

select to_char(test_date, 'yyyy') year, count(test_date) test_record grouping to_char(test_date, 'yyyy') order year asc;

you want grouping year , not test date.

select count(*), to_date('yyyy',test_data) year test_record grouping to_date('yyyy',test_date)

sql database count sum