Solved

Oracle SQL Query Syntax

Posted on 2016-08-23
6
95 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 35

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

867 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

19 Experts available now in Live!

Get 1:1 Help Now