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 ...
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
jsmith08Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
The "issue" I see in the request is weekly vs monthly ...
the weeks are not really aligned to the months, so I don't see how you want to "display" the weekly and monthly totals in the same report...

from the pure tec aspect, I would consider the to_char function to provide the week number info:
to_char(pc.arrest_dt, 'WW'),  or IW:
https://www.techonthenet.com/oracle/functions/to_char.php

so, you can "group by" from the original set based on that.
presumably with a CTE expression ....
;WITH cte as ( <yourselect above with also to_char( ... 'IW' ) added )
select  cte.WEEKDAY            , cte.DATE            ,cte.COUNT_OF_ARRESTS
 from cte
union all
select 'Weekly Total',  'Week ' || cte.week || ' of year ' || cte.year 
    , sum(COUNT_OF_ARRESTS)
 from cte 
group by cte.year, cte.week

union all
select 'Monthly Total',  'Month ' || cte.month || ' of year ' || cte.year 
    , sum(COUNT_OF_ARRESTS)
 from cte 
group by cte.year, cte.month

Open in new window

0
 
PortletPaulCommented:
If you want SQL to produce sub-totals then you need to use GROUP BY ROLLUP, and if you are doing that you need columns that will enable the rollup to occur, so you may end-up with columns you would prefer not to see.

Pity you don't supply some sample data (hint) because when that is provided we can produce a sample result along with a query.

Something like this may work for you:
SELECT
        trunc(pc.arrest_dt, 'YEAR')  arrest_yr
      , trunc(pc.arrest_dt, 'MONTH') arrest_mnth
      , trunc(pc.arrest_dt, 'WW')    arrest_week
      , trunc(pc.arrest_dt)          arrest_dt
      , COUNT(DISTINCT i.ssn)        Arrest_Count
FROM pc
WHERE 1 = 1
GROUP BY ROLLUP
        trunc(pc.arrest_dt, 'YEAR')
      , trunc(pc.arrest_dt, 'MONTH')
      , trunc(pc.arrest_dt, 'WW')
      , trunc(pc.arrest_dt)
;

Open in new window

by the way.
"distinct" is NOT a function so using parentheses like this: distinct(i.ssn) is actually incorrect and they are just ignored
1
 
Mark GeerlingsDatabase AdministratorCommented:
Because weeks and months don't match up nicely on the same beginning and ending dates, I don't know of an easy way to get both weekly totals and monthly totals in the same query.  I think either separate queries, or a "union all" to combine multiple queries like Guy Hengel suggested will be required.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
jsmith08Author Commented:
Ok, thanks for all your comments. I will be working with this again today and follow up here a little later.
0
 
GhunaimaCommented:
Try these

Daily Counts:

select pc.arrest_dt, COUNT(DISTINCT SN) FROM ...  WHERE ... GROUP BY PC_ARREST_DT;

Monthly Counts:

select trunc(pc.arrest_dt, 'month'), sum(dcnt) mcnt FROM
(select pc.arrest_dt, COUNT(DISTINCT SN) dcnt FROM ...  WHERE ...)
GROUP BY trunc(pc.arrest_dt, 'month');


Yearly Counts:

select trunc(pc.arrest_dt, 'year'), sum(dcnt) ycnt FROM
(select pc.arrest_dt, COUNT(DISTINCT SN) dcnt FROM ...  WHERE ...)
GROUP BY trunc(pc.arrest_dt, 'month')
order by MO, Dt, Day_of_Week;
0
 
jsmith08Author Commented:
I ended up using the following to get counts for each day during the period. I exported to Excel  displaying the weekday, dt, and counts in the Excel.

select to_char(jsa.arrest_date, 'MM') Mo,
to_char(jsa.arrest_date, 'Mon') Month,
to_char(jsa.arrest_date, 'D') Day_of_Week,
to_char(jsa.arrest_date, 'Day') Weekday,
to_char(jsa.arrest_date, 'mm/dd/yy') Dt,

count(distinct case when jsa.dfndt_ssn is not null then jsa.dfndt_ssn else jsa.idnt_id end) as "DEFENDANTS ARRESTED",

count(distinct case when jsa.act_cap_flg = 'N' and jsa.fta_hist_flg = 'N' and jsa.open_cases_flg = 'N' and jsa.dfndt_ssn is not null then jsa.dfndt_ssn
when jsa.act_cap_flg = 'N' and jsa.fta_hist_flg = 'N' and jsa.open_cases_flg = 'N' and jsa.dfndt_ssn is null then jsa.idnt_id end) as "ELIGIBLE FOR RELEASE ON PB",

<then a few other case statements for what I needed to count>

from js_arrests_review jsa

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

I just used separate queries for monthly totals.

I can go ahead and close this question.
0
All Courses

From novice to tech pro — start learning today.