Modifying SQL Query

Question

Previously i wanted to find

1- Batch id's 2- Upload time 3- Count of requests infront of each batch

i wrote the following query and it worked perfectly

SELECT REQUEST_BATCH_ID,To_CHAR(B.UPDATE_STAMP , 'DD/MM/YYYY HH24'),COUNT(*)
FROM bpdata.bulk_prov_detail B  
WHERE B.REQUEST_BATCH_ID
IN
(
SELECT REQUEST_BATCH_ID 
FROM bpdata.bulk_prov_master A
where A.START_TIME > to_DATE ('12/16/2014 00', 'MM/DD/YYYY HH24') 
)
GROUP BY To_CHAR(B.UPDATE_STAMP , 'DD/MM/YYYY HH24'),REQUEST_BATCH_ID
order by 1 desc,2 desc

and I get the data in following format

Batch_ID    Day/Date    Count(*)
257658  17/12/2014 11   5
257658  17/12/2014 12   4
257657  17/12/2014 12   4
257656  17/12/2014 12   2

But now I want to count the total number of count() and the largest number of count() and I am unable to devise a query for that. Your help and guidance would be appreciated in that regard

EDITED: Example of Required Output

Batch_ID    Day/Date    Count(*)  
257658  17/12/2014 11   5
257658  17/12/2014 12   4
257657  17/12/2014 12   4
257656  17/12/2014 12   2


Sum(Count(*))    Largest_request     Time
   15                   5              17/12/2014 11

Sum(Count(*)) and Largest_request and time is what I need now

Can you show an example of your required output please?

Mureinik
– Mureinik

2014-12-17 08:08:50 +00:00
Commented Dec 17, 2014 at 8:08 — Mureinik
– Mureinik, Commented Dec 17, 2014 at 8:08

Nick Bloor · Accepted Answer · 2014-12-17 08:43:15Z

I haven't tested, but it should be something like:

SELECT SUM(x.total) AS grandtotal
FROM (
SELECT REQUEST_BATCH_ID,To_CHAR(B.UPDATE_STAMP , 'DD/MM/YYYY HH24'),COUNT(*) AS total
FROM bpdata.bulk_prov_detail B  
WHERE B.REQUEST_BATCH_ID
IN
(
SELECT REQUEST_BATCH_ID 
FROM bpdata.bulk_prov_master A
where A.START_TIME > to_DATE ('12/16/2014 00', 'MM/DD/YYYY HH24') 
)
GROUP BY To_CHAR(B.UPDATE_STAMP , 'DD/MM/YYYY HH24'),REQUEST_BATCH_ID
) x

FOR Max Count:

SELECT *
FROM (
SELECT REQUEST_BATCH_ID,To_CHAR(B.UPDATE_STAMP , 'DD/MM/YYYY HH24'),COUNT(*) AS total
FROM bpdata.bulk_prov_detail B  
WHERE B.REQUEST_BATCH_ID
IN
(
SELECT REQUEST_BATCH_ID 
FROM bpdata.bulk_prov_master A
where A.START_TIME > to_DATE ('12/16/2014 00', 'MM/DD/YYYY HH24') 
)
GROUP BY To_CHAR(B.UPDATE_STAMP , 'DD/MM/YYYY HH24'),REQUEST_BATCH_ID
) x ORDER BY x.total DESC LIMIT 0,1

To get exactly what you are asking for, you join the 2 queries. (also untested)

SELECT
(
SELECT SUM(x.total) AS grandtotal
FROM (
SELECT REQUEST_BATCH_ID,To_CHAR(B.UPDATE_STAMP , 'DD/MM/YYYY HH24'),COUNT(*) AS total
FROM bpdata.bulk_prov_detail B  
WHERE B.REQUEST_BATCH_ID
IN
(
SELECT REQUEST_BATCH_ID 
FROM bpdata.bulk_prov_master A
where A.START_TIME > to_DATE ('12/16/2014 00', 'MM/DD/YYYY HH24') 
)
GROUP BY To_CHAR(B.UPDATE_STAMP , 'DD/MM/YYYY HH24'),REQUEST_BATCH_ID
) x
) y,
(
SELECT *
FROM (
SELECT To_CHAR(B.UPDATE_STAMP , 'DD/MM/YYYY HH24') AS daydate,COUNT(*) AS total
FROM bpdata.bulk_prov_detail B  
WHERE B.REQUEST_BATCH_ID
IN
(
SELECT REQUEST_BATCH_ID 
FROM bpdata.bulk_prov_master A
where A.START_TIME > to_DATE ('12/16/2014 00', 'MM/DD/YYYY HH24') 
)
GROUP BY To_CHAR(B.UPDATE_STAMP , 'DD/MM/YYYY HH24'),REQUEST_BATCH_ID
) x ORDER BY x.total DESC LIMIT 0,1
) z

Thank you so much, i got 1 part right from your query.. so 1+

psaraj12 · Accepted Answer · 2014-12-20 06:50:40Z

0

You can use Subquery Factoring WITH Clause to achieve the same

with batch_query as 
(
SELECT REQUEST_BATCH_ID,
               To_CHAR(B.UPDATE_STAMP, 'DD/MM/YYYY HH24') upd_stmp,
               COUNT(*) cnt
          FROM bpdata.bulk_prov_detail B
         WHERE B.REQUEST_BATCH_ID IN
               (SELECT REQUEST_BATCH_ID
                  FROM bpdata.bulk_prov_master A
                 where A.START_TIME >
                       to_DATE('12/16/2014 00', 'MM/DD/YYYY HH24'))
         GROUP BY To_CHAR(B.UPDATE_STAMP, 'DD/MM/YYYY HH24'),
                  REQUEST_BATCH_ID)
,batch_agg_query as
(SELECT max(cnt) max_cnt,sum(cnt) sum_cnt from batch_query )
 select sum_cnt,max_cnt,batch_query.upd_stmp
  FROM batch_query,batch_agg_query 
  where batch_query.cnt=batch_agg_query.max_cnt

edited Dec 20, 2014 at 6:50

answered Dec 17, 2014 at 8:42

psaraj12

5,0783 gold badges25 silver badges34 bronze badges

2 Comments

Hassaan Rabbani Over a year ago

what does with batch_query as means? can you please elaborate?

psaraj12 Over a year ago

it is called subquery factoring in oracle blog.mclaughlinsoftware.com/tag/subquery-factoring-clauses

Collectives™ on Stack Overflow

Modifying SQL Query

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related