CODEWITHSUNDEEP
sqloracle-database
jordaniac89
jordaniac89

Reputation: 574

GROUP BY month WHERE range of dates

I have a table with the following columns:

enter image description here

NETID is a unique identifier for the user, OCCURRENCES is the number of times they've logged in in a month, and EARLIEST_MONTHLY_DATE is the earliest time they logged in for a month. I've currently using:

SELECT 
    to_char(earliest_monthly_date, 'yyyy-mm-dd') MARCH, 
    COUNT(NETID) unique_login_count
FROM 
    REPORT_SERVICE_USAGE 
WHERE 
    earliest_monthly_date >= to_date('2014-03-01', 'yyyy-mm-dd')
AND earliest_monthly_date <= to_date('2014-03-31', 'yyyy-mm-dd')
GROUP BY 
    to_char(earliest_monthly_date, 'yyyy-mm-dd')
ORDER BY 
    to_char(earliest_monthly_date, 'yyyy-mm-dd') ASC

which gives me the total number of logins per day in a given month. It returns something like this:

enter image description here

Now, I want to set up my query so that it groups the login count by month instead of by day of a given month. I'm not sure how to do this (or if it can be done), as I'm not very familiar with SQL, but any help would be greatly appreciated. If you need any more info, feel free to ask. By the way, it's an Oracle database.

Upvotes: 0

Views: 96

Answers (2)

Jorge Campos
Jorge Campos

Reputation: 23381

Just change the select and group by 'to_char' to extract

SELECT 
    extract(MONTH from earliest_monthly_date) MARCH, 
    COUNT(NETID) unique_login_count
FROM 
    REPORT_SERVICE_USAGE 
WHERE 
    earliest_monthly_date >= to_date('2014-03-01', 'yyyy-mm-dd')
AND earliest_monthly_date <= to_date('2014-03-31', 'yyyy-mm-dd')
GROUP BY 
    extract(MONTH from earliest_monthly_date)
ORDER BY 
    extract(MONTH from earliest_monthly_date) ASC

Edit

If you like the groups to by by month and year the answer from @Lamak is a better option.

Upvotes: 1

Lamak
Lamak

Reputation: 70678

SELECT 
    to_char(earliest_monthly_date, 'yyyy-mm') MARCH, 
    COUNT(NETID) unique_login_count
FROM 
    REPORT_SERVICE_USAGE 
WHERE 
    earliest_monthly_date >= to_date('2014-03-01', 'yyyy-mm-dd')
AND earliest_monthly_date <= to_date('2014-03-31', 'yyyy-mm-dd')
GROUP BY 
    to_char(earliest_monthly_date, 'yyyy-mm')
ORDER BY 
    to_char(earliest_monthly_date, 'yyyy-mm') ASC

Upvotes: 2

Related Questions