Command to count users per year

Transact-SQL
1

Hi,

I am new here and not so good with sql. I am just wondering if you can help me on how to create a command to get the total number of users per year?

Thanks in advance,
netchie

2

Assuming you have a table like:

Username LoginDate
bob 1 jan 1015
ted 6 jun 2014

select count(Username) as Users_Per_Year, year(LoginDate) as Login_Year
from yourtable
group by year(Logindate)
order by year(LoginDate) asc;
3

Hi gbritton,

Wow! Fast reply. Thank you so much for your time.

So, I tried your advise but I got an error message. Here is what I put:

select count(name) as users_per_year, year(timestamp) as Login_year
from sysadm.Login
group by year(Logindate)
order by year(loginDate asc;

The error message:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'asc'.

Table sysadm.Login has only the following Columns:

timestamp
id
passwd
per_rel-id
entity_id
is_guest
create_person_ent
date_last_login
date_updated
attempt_count
date_password
desktop_layoutid
can_view_protected
date_format
can_export
user_role_id
is_us_based
user_creators_entity
name
can_edit_protected_persons
authentication_type

4

You are missing the closing ")" before the keyword "ASC"

5

You're SELECTing a different year than you're GROUPing BY. You should be consistent:

SELECT count(name) as users_per_year, year(logindate) as Login_year
FROM sysadm.Login
GROUP BY year(Logindate)
ORDER BY year(loginDate);