Meow Meow - 1 month ago 5
SQL Question

How to find which year do values tend to increase in ? in SQL

Basically I have a table like this:

Table Time:

ID.......Date

1......08/26/2016
1......08/26/2016
2......05/29/2016
3......06/22/2016
4......08/26/2015
5......05/23/2015
5......05/23/2015
6......08/26/2014
7......04/26/2014
8......08/26/2013
9......03/26/2013


The query should return like this

Year........CountNum

2016........4
2015........3


To find out which year does its value tend to increase in. I notice that I want to display the years that have more values (number of row in this case) than the previous year.

What I've done so far

SELECT Year, count(*) as CountNum
FROM Time
GROUP BY Year
ORDER BY CountNum DESC;


I don't know how to get the year from date format. I tried
year(Date)
function, but I got Null data.
Please help!

Answer

It should works fine.

select year(date), count(*) as countNum
from time
group by year(date)
order by countNum