Avatar of Janice Smith
Janice Smith
Flag for United States of America

asked on 

Oracle SQL Query Syntax

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 ...

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
Oracle DatabaseSQL

Avatar of undefined
Last Comment
Janice Smith

8/22/2022 - Mon