CODEWITHSUNDEEP
oracle-databaseoracle11gaggregate-functions
Sammy Pawar
Sammy Pawar

Reputation: 1251

11g Oracle aggregate SQL query

Can you please help me in getting a query for this scenario. In below case it should return me single row of A=13 because 13,14 in column A has most occurrences and value of B (30) is greater for 13. We are interested in maximum occurrences of A and in case of tie B should be considered as tie breaker.

A   B
13  30
13  12
14  10
14  25
15  5

In below case where there are single occurrence of A (all tied) it should return 14 having maximum value of 40 for B.

A   B
13  30
14  40
15  5

Use case - we get calls from corporate customers. We are interested in knowing during what hours of day when most calls come and in case of tie - which of the busiest hours has longest call.

Further question

There is further questions on this. I want to use either of two solutions - '11g or lower' from @GurV or 'dense_rank' from @mathguy in bigger query below how can I do it. 

    SELECT dv.id , u.email , dv.email_subject AS headline , dv.start_date , dv.closing_date, b.name AS business_name, ls.call_cost, dv.currency,
    SUM(lsc.duration) AS duration,  COUNT(lsc.id) AS call_count, ROUND(AVG(lsc.duration), 2) AS avg_duration 
    -- max(extract(HOUR from started )) keep (dense_rank last order by count(duration), max(duration)) as most_popular_hour
    FROM deal_voucher dv
        JOIN lead_source ls ON dv.id = ls.deal_id
        JOIN lead_source_call lsc ON ls.PHONE_SID = lsc.phone_number_id
        JOIN business b ON dv.business_id = b.id
        JOIN users u ON b.id = u.business_id
        AND TRUNC(dv.closing_date) = to_date('13-01-2017', 'dd-mm-yyyy')
        AND lsc.status = 'completed' and lsc.duration >= 30
    GROUP BY dv.id , u.email , dv.email_subject , dv.start_date , dv.closing_date, b.name, ls.call_cost, dv.currency
--, extract(HOUR from started )

Upvotes: 0

Views: 84

Answers (2)

user5683823
user5683823

Reputation:

Here is a query that will work in older versions (no fetch clause) and does not require a subquery. It uses the first/last function. In case of ties by both "count by A" and "value of max(B)" it selects only the row with the largest value of A. You can change that to min(A), or even to sum(A) (although that probably doesn't make sense in your problem) or LISTAGG(A, ',') WITHIN GROUP (ORDER BY A) to get a comma-delimited list of the A's that are tied for first place, but that requires 11.2 (I believe).

select   max(a) keep (dense_rank last order by count(b), max(b)) as a
       , max(max(b)) keep (dense_rank last order by count(b))    as b
from     inputs
group by a
;

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39487

Try this if 12c+

select a
from t
group by a
order by count(*) desc, max(b) desc
fetch first 1 row only;

If 11g or lower:

select * from (
    select a
    from t
    group by a
    order by count(*) desc, max(b) desc
) where rownum = 1;

Note that if there is equal count and equal max value for two or more values of A, then any one of them will be fetched.

Upvotes: 1

Related Questions