Number of records by Year - SQL Query Help

• vmrao

  SSC Enthusiast

  Points: 133

  More actions

  September 13, 2007 at 12:39 pm

  #125661

  Hi,

  I have a table called 'suggestions' where I have 'suggestionDate' and some other fields. 'suggestionDate' has value like '9/12/2007'. The table will have information on number of suggestions per year.  I need an output like 'Number of Suggestions Per Year' as follows.

  2007 12

  2006  9

  2005 11

  2004 10

  How can I do that ? I have only a clue that I need to use DISTINCT, YEAR function, GROUP BY and ORDER BY. But, I could not figure out the exact query to get the above resultset. Please help.

  Thanks

  vmrao

• noeld

  SSC Guru

  Points: 96590

  More actions

  September 13, 2007 at 12:41 pm

  #733591

  select year(SuggestionDate) as YearOfSuggestion, count(*) as Total

  from suggestions

  group by year(SuggestionDate)

  order by year(SuggestionDate)

  
  * Noel