Oracle queries --Challenging Question

Hi team,

I need to write a query using Oracle SQL , where in I need to generate an DRILL DOWN report . I need to generate this using queries no reporting tools.  
Any sample example to do so will be really helpful.
sam_2012Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PaulConnect With a Mentor Commented:
Such a result is produced by an option in the group by clause:

GROUP BY ROLLUP
0
 
chaauCommented:
It is possible to generate a complex drill down or hierarchical query in Oracle. I would suggest you provide a sample data  and a desired result
0
 
sdstuberCommented:
when you provide your sample data and expected results, please also specify your database version (all 4 numbers, for example 11.2.0.4  or 12.1.0.2)
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
PaulCommented:
In information technology to drill down means to move from one place to another, information to detailed data by focusing in on something. In a GUI-environment, "drilling-down" may involve clicking on some representation in order to reveal more detail.[1]
https://en.wikipedia.org/wiki/Drill_down

My interpretation of "drill-down" requires some "click to open the next level" activity which is not possible by SQL alone. So rather than ask us for "any example" I suggest you need to guide us on what it is you want.
0
 
Ryan ChongCommented:
you may try to look for some BI (Business Intelligence) solutions for examples: QlikView, Tableau, etc which read your data as the source and present it in the relevant dashboards, which include the drill down features.

I believe Oracle do have their own BI solutions but I'm not familiar with.

>>I need to generate this using queries no reporting tools.
ok, noted with that but just wondering why no?
0
 
Geert GOracle dbaCommented:
some of the most advanced queries for items like this you'll find in the warehouse guide
http://docs.oracle.com/database/121/DWHSG/aggreg.htm#DWHSG020

to be able to drill down, you usually need to cut it up into details first like facts and time
before being able to roll up or drill down

you could produce a webpage with the static data in a csv and then use d3 to display it graphically
https://d3js.org/

like a grouped bar chart:
http://bl.ocks.org/mbostock/3887051
0
 
sam_2012Author Commented:
Hi Team,

 I have attached the sample report and data. Kindly help me in this regard.
Data-Sheet.xlsx
0
 
sam_2012Author Commented:
Hi Sdstuber,

I have attached the sample data and report format for your reference.
0
 
sam_2012Author Commented:
Hi PortletPaul,

I have written the below query for the output , But I need to format the output , I want the grand Total to appear in the last is there a way for it.

Select Zone, District , IndustrialArea , count(inspection) inspection,
count(dataexcel) dataexcel,
count(status) status,
count(EXCELintegration) EXCELintegration,
count(ValidationStatus) ValidationStatus
from Temp_data
group by rollup(Zone, District , IndustrialArea)
order by zone NULLS FIRST, district NULLS FIRST , industrialarea NULLS FIRST
0
 
PaulCommented:
NULLS FIRST is a problem because each total/sub total will display nulls

Try this perhaps (no guarantee)
SELECT
        Zone
      , District
      , IndustrialArea
      , COUNT(inspection)       inspection
      , COUNT(dataexcel)        dataexcel
      , COUNT(status)           status
      , COUNT(EXCELintegration) EXCELintegration
      , COUNT(ValidationStatus) ValidationStatus
FROM Temp_data
GROUP BY
        ROLLUP (Zone, District, IndustrialArea)
ORDER BY
      , grouping(Zone)
      , zone NULLS FIRST
      , grouping(District)
      , district NULLS FIRST 
      , grouping(IndustrialArea)
      , industrialarea NULLS FIRST

Open in new window

These may be used as columns in the select clause also so you can see what the do:
      , grouping(Zone)
      , grouping(District)
      , grouping(IndustrialArea)

Open in new window

You may have to "fiddle" to get the best outcome.
0
 
sam_2012Author Commented:
awesome
0
 
PaulCommented:
Yes group by rollup is really handy at times. cheers, Paul
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.