Trying to list the amount of specific years are in a table

Question

I'm Trying to select a datetime with a specific year from the table and display the amount of those dates that have that year.

I can select just the year from the datetime, but it gives me the amount of every single specific date, not just the year. Like:

|       Date | Amount |
| 2012-04-07 |      5 |
| 2012-05-21 |      5 |

but I just want 2012, 10.

Select YEAR(Orderdate), count(*)
From Orders
Group by Orderdate

The output needs to show a single row with 2012 in one column, and the total count of 2012's in the second column. Like this:

| Year | Amount |
| 2012 |     10 |
| 2013 |      8 |
| 2014 |      2 |

score 3 · Answer 1 · edited Apr 18 '19 at 11:43

Use proper date logic in your WHERE clause :

SELECT YEAR(OrderDate) AS [Year],
       COUNT(OrderID) AS Orders --Assumed Column Name
FROM Orders
WHERE OrderDate >= '20120101'
  AND OrderDate < '20130101'
GROUP BY YEAR(OrderDate);

A clause like YEAR(OrderDate) isn't SARGable, meaning that the indexes in your table can't be used. That could have severe implications on your query's performance. As a result using Boolean logic without applying a function to your column in the WHERE is the best choice.

score 2 · Answer 2 · answered Apr 18 '19 at 09:37

2

For SQL Server;

SELECT YEAR(Orderdate), COUNT(1)
FROM Orders
GROUP BY YEAR(Orderdate)

answered Apr 18 '19 at 09:37

Chinthaka Fernando

744
8
12

Thank you! I'm new to count so I didn't even think of putting a 1 in there! :D – Caleb Norris Apr 18 '19 at 10:00
I'm discovering `COUNT(1)` now. Useful answer [here](https://stackoverflow.com/a/1221649/3283073). – Johwhite Apr 18 '19 at 10:58
@Johwhite . . . `COUNT(1)` and `COUNT(*)` are equivalent. – Gordon Linoff Apr 18 '19 at 11:08

score 0 · Answer 3 · answered Apr 18 '19 at 08:25

0

You can achieve it by writing a nested query

SELECT Year , Count(*) AS Total_Count
FROM (Select YEAR(Orderdate) AS Year, *
      From Orders)
WHERE Year = '2012'
GROUP BY Year

answered Apr 18 '19 at 08:25

umair

525
5
18

Trying to list the amount of specific years are in a table

3 Answers3