Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Oracle SQL Query Syntax

Avatar of Janice Smith
Janice SmithFlag for United States of America asked on
Oracle DatabaseSQL
6 Comments1 Solution191 ViewsLast Modified:
Hello Experts,

I need to show the count of arrests by day, week, and month. The daily count should be a distinct count because an ssn should only be counted once in a single day. The weekly count or subtotal should be a count(*) value because if an ssn shows up on Monday, Tuesday, and Wednesday in a weekly period, it should be counted 3 times, once for each day that it appears. The monthly count or total should also be a count(*) value because the ssn should be counted once for every day that it appears during the month. I'm trying to get the ouput to include the weekly and monthly totals and appear like the data below so that I don't have to calculate those totals in Excel by inserting summaries.

WEEKDAY            DATE            COUNT OF ARRESTS

Monday         02/01/16                       72
Tuesday        02/02/16                       18
Wednesday      02/03/16                       26
Thursday       02/04/16                       37
Friday                 02/05/16                       28
Saturday       02/06/16                       23

Weekly Total:                                 204

...this would repeat for each weekly period in the month

Feb 2016 Total:                  1148

this would repeat for the following months            

The select statement that I'm using to get everything but the weekly and monthly totals is below:

select to_char(pc.arrest_dt, 'MM') Mo,
to_char(pc.arrest_dt, 'Mon') Month,
to_char(pc.arrest_dt, 'D') Day_of_Week,
to_char(pc.arrest_dt, 'Day') Weekday,
to_char(pc.arrest_dt, 'mm/dd/yy') Dt,
count(distinct(i.ssn)) Arrest_Count

from ...
where...

group by to_char(pc.arrest_dt, 'MM'),
to_char(pc.arrest_dt, 'Mon'),
to_char(pc.arrest_dt, 'D'),
to_char(pc.arrest_dt, 'Day'),
to_char(pc.arrest_dt, 'mm/dd/yy')
order by MO, Dt, Day_of_Week;

Thank you