Grouping Records By...

Did you know? SQL is a great query language for writing reports. A report include numbers, days, dates, times, events, counts etc.By combining these components a report writer makes a report that one can visualize and tells the story about a company or a product. To illustrate a sample report query, we will introduce Mr Junior who works as a report write for a company that manages a call center. What kind of report does he produce? I have over 1/2 dozens years of experience working in a company that writes software for call centers, therefore Mr Junior reports would look like number of calls received by his center by the minute, hour, day, week, month, quarter and year etc. There is no single query that produces those numbers, therefore I will break his  query to generate a clean report to include all major stats a call center managers and directors need to measure the performance of their call center.

To make the case simple, we have a table called CALL_RECORD that records all the call details.This table stores callers' phone number, date and time of of call arrival along with various other detail which we are not interested. TRUNC, TO_CHAR and TO_DATE are Oracle supplied functions that plays a vital role and one must understand and know these functions inside out when working with date and time. We will recap these functions before we dig into reporting SQL.
My Oracle Environment settings

SQL> select sysdate from dual;
SYSDATE
---------
05-MAY-17
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2017-05-05 11:11:19

GUI fans, this can be automated in SQL Developer IDE under TOOL-->PREFERENCE--> DATABASE--> NLS --> TIMEFORMAT --> YYYY-MM-DD HH24:MI:SS
TRUNC: The TRUNC(date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different date time datatype for date.If you omit format, then date is truncated to the nearest day.
Syntax:
TRUNC(date)
Example:

SQL> SELECT SYSDATE, TRUNC(SYSDATE) from dual;
SYSDATE             TRUNC(SYSDATE)    
------------------- -------------------
2017-05-04 16:33:39 2017-05-04 00:00:00

TO_CHAR: The Oracle TO_CHAR function converts a number or date to a string.
Syntax: TO_CHAR( value [, format_mask] [, nls_language] )
Example:

select TO_CHAR(date, 'date_format') from dual;
select TO_CHAR(sysdate, 'yyyy-mm-dd') from dual;
2017-05-05

Note: See the end for the list of valid date format parameters.
TO_DATE: The to_date function is used to convert character data to the date.
Syntax: TO_DATE( string1 [, format_mask] [, nls_language] )
Example:

SQL> SELECT TO_DATE('20150515','yyyy-mm-dd') FROM dual;
2015-05-15 00:00:00

Note: See the end for the list of valid date format parameters.
Group By Minute:

SELECT  TRUNC(call_arrival_dt, 'MI') AS DATE_TIME, COUNT(*)
FROM call_record
WHERE TRUNC(call_arrival_dt) > TRUNC(sysdate - 2)
GROUP BY TRUNC( call_arrival_dt, 'MI')
ORDER BY TRUNC( call_arrival_dt, 'MI');

Result:

DATE_TIME  COUNT
2017-05-04 14:44:00 1
2017-05-04 14:45:00 3
2017-05-04 14:46:00 55
2017-05-04 14:47:00 56
2017-05-04 14:48:00 44
2017-05-04 14:49:00 3
2017-05-04 14:50:00 44

Group By Hour:

SELECT TRUNC(call_arrival_dt, 'HH'), COUNT(*)
FROM call_record
WHERE TRUNC(call_arrival_dt) > TRUNC(sysdate - 2)
GROUP BY TRUNC( call_arrival_dt, 'HH')
ORDER BY TRUNC( call_arrival_dt, 'HH');

Result:

DATE_TIME  COUNT
2017-05-04 14:00:00 1333
2017-05-04 15:00:00     2323
2017-05-04 16:00:00     232
2017-05-04 17:00:00 343
2017-05-04 18:00:00 343
2017-05-04 18:00:00 565
2017-05-04 20:00:00 565

Group By Day:

SELECT TRUNC(call_arrival_dt, 'DD'), COUNT(*), 
FROM call_record
WHERE TRUNC(call_arrival_dt) > TRUNC(sysdate - 3)
GROUP BY TRUNC( call_arrival_dt, 'DD')
ORDER BY TRUNC( call_arrival_dt, 'DD');

Result:

DATE_TIME  COUNT
2017-05-03 00:00:00 45234524
2017-05-04 00:00:00 45234433

Group By Week:

SELECT COUNT(*), TO_CHAR(sysdate, 'IW') AS WEEK
FROM call_record
GROUP BY TO_CHAR(sysdate, 'IW')
ORDER BY TO_CHAR(sysdate, 'IW');

Result:

WEEK COUNT
12 41324141
13      32423414
14 32675243

Group By Month:

SELECT COUNT(*), TRUNC(call_arrival_dt, 'MM')
FROM call_record
WHERE TRUNC(call_arrival_dt) > TRUNC(sysdate - 100)
GROUP BY TRUNC( call_arrival_dt, 'MM')
ORDER BY TRUNC( call_arrival_dt, 'MM');

Result:

DATE_TIME  COUNT
2017-04-01 00:00:00 45234524352
2017-05-01 00:00:00 45234433424

Group By Quarter:

SELECT COUNT(*), TO_CHAR(sysdate + 60, 'Q') AS QUARTER
FROM call_record 
GROUP BY TO_CHAR(sysdate + 60, 'Q')
ORDER BY TO_CHAR(sysdate + 60, 'Q');

Group By Year:

SELECT COUNT(*), TO_CHAR(sysdate, 'YYYY') AS YEAR
FROM call_record 
GROUP BY TO_CHAR(sysdate, 'YYYY')
ORDER BY TO_CHAR(sysdate, 'YYYY');

List of VAlid parameteres Date Format Refrences

YEAR Year, spelled out
YYYY 4-digit year
YYY
YY
Y Last 3, 2, or 1 digit(s) of year.
IYY
IY
I Last 3, 2, or 1 digit(s) of ISO year.
IYYY 4-digit year based on the ISO standard
Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM Month (01-12; JAN = 01).
MON Abbreviated name of month.
MONTH Name of month, padded with blanks to length of 9 characters.
RM Roman numeral month (I-XII; JAN = I).
WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW Week of year (1-52 or 1-53) based on the ISO standard.
D Day of week (1-7).
DAY Name of day.
DD Day of month (1-31).
DDD Day of year (1-366).
DY Abbreviated name of day.
J Julian day; the number of days since January 1, 4712 BC.
HH Hour of day (1-12).
HH12 Hour of day (1-12).
HH24 Hour of day (0-23).
MI Minute (0-59).
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
FF Fractional seconds.

Congratulations, you have completed a report writing course for absolutely free. Share this with your buddy if you find value in this article.