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
Janice SmithSystems AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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
Guy Hengel [angelIII / a3]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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Janice SmithSystems AnalystAuthor Commented:
Ok, thanks for all your comments. I will be working with this again today and follow up here a little later.
G Trurab KhanSnr. Development ManagerCommented:
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;
Janice SmithSystems AnalystAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.