Solved

Oracle SQL Query Syntax

Posted on 2016-08-23
6
85 Views
Last Modified: 2016-09-16
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
0
Comment
Question by:jsmith08
6 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41768356
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41768368
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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 41770128
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:jsmith08
ID: 41770281
Ok, thanks for all your comments. I will be working with this again today and follow up here a little later.
0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 41780387
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
 

Author Comment

by:jsmith08
ID: 41802348
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now