CODEWITHSUNDEEP
sqloracle-database
Hassaan Rabbani
Hassaan Rabbani

Reputation: 2465

Modifying SQL Query

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

Upvotes: 0

Views: 83

Answers (2)

psaraj12
psaraj12

Reputation: 5072

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

Upvotes: 0

Nick Bloor
Nick Bloor

Reputation: 113

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

Upvotes: 1

Related Questions