Oracle SQL Developer – group by and count within specific date

SQL noob, trying to get some help grouping by unique name, providing a count, filtered for only start dates during 2021. This is what I have so far, and why I’m getting lost.

my_table:

| name     | start                           |
|----------|---------------------------------|
| Michigan | 01-JAN-21 01.00.00.000000000 AM |
| State    | 01-JAN-20 01.00.00.000000000 AM |
| State    | 01-JAN-21 01.00.00.000000000 AM |
| State    | 01-FEB-21 01.00.00.000000000 AM |
| Indiana  | 01-FEB-21 01.00.00.000000000 AM |
| Michigan | 01-FEB-21 01.00.00.000000000 AM |
| State    | 01-MAR-21 01.00.00.000000000 AM |

Trying to get count of the Name, filtered for only 2021 start dates. This is what I want my output to look like:

| name     | Count |
|----------|-------|
| Michigan | 2     |
| State    | 3     |
| Indiana  | 1     |

My code:

select name, count(name) from my_table
group by name, start
having start >= '01-JAN-21 01.00.00.000000000 AM'

But that duplicates the names (because of the unique start dates) and doesn’t provide an aggregate count. I’m unable to remove the group by ‘start’, because that outputs an error in SQL. so I’m not sure how to get to what I want my output to look like. Would greatly appreciate some guidance.

Answer

Having is for filtering after grouping so that you cN filter based on aggregates, you can filter before grouping here, so use where.

Remove Start from the group by, you need only name.

There is a function you can use to get the year from a date, https://docs.oracle.com/javadb/10.8.3.0/ref/rrefyearfunc.html

Select name, count(name)
From my_table
where year(start) = 2021
Group by name

Or if start is a char or varchar then you can do

Select name, count(name)
From my_table
where substr(start, 8, 2) = '21'
Group by name