CODEWITHSUNDEEP
sqloracle-database
Mohamad Ghanem
Mohamad Ghanem

Reputation: 599

prevent query from return duplicate results

I have a database contains information for a telecommunication company with the following table:

I want to retrieve the names of top 3 subscribers who make the highest count number of calls (with duration less than 60 seconds for each call) in specific given day.

So, I write the following query :

with TEMPRESULT AS 
(
    select * from 
    (
      select CALLS.LINE_FROM , count(*) totalcount
      from CALLS
      where (((END_DATE_CALL-START_DATE_DATE)*24*60*60)<=60 and to_char(S_DATE,'YYYY-MM-DD')='2015-12-12') 
      group by CALLS.LINE_FROM
      order by totalcount DESC
    ) 
    where rownum <= 3
) 

select F_NAME,L_NAME
from TEMPRESULT inner join SUBSCRIBERS_LINES on TEMPRESULT.LINE_FROM=SUBSCRIBERS_LINES.line_id inner join SUBSCRIBERS on SUBSCRIBERS_LINES.SUB_ID=SUBSCRIBERS.SUB_ID;

But this query will not work if one of the subscribers has more than one line,

for example: (X1 has L1 and L2 lines

X2 has L3

X3 has L4)

if X1 talks 20 calls from L1, and 19 calls from L2

X2 talks 15 calls from L3

X3 talks 10 calls from L4

my query will return the following output:

X1

X1

X2

it must return :

X1

X2

X3

how to modify the query to not return duplicate name ?

Upvotes: 1

Views: 65

Answers (3)

Bryan Dellinger
Bryan Dellinger

Reputation: 5294

how about something like this (T represents the result from your query)

WITH t AS
  (SELECT 1 id, 'x1' subscriber, 'l1' line FROM dual
  UNION ALL
  SELECT 2, 'x1', 'l1' FROM dual
  UNION ALL
  SELECT 3, 'x1', 'l1' FROM dual
  UNION ALL
  SELECT 4, 'x1', 'l2' FROM dual
  UNION ALL
  SELECT 5, 'x1', 'l2' FROM dual
  UNION ALL
  SELECT 6, 'x1', 'l2' FROM dual
  UNION ALL
  SELECT 6, 'x1', 'l2' FROM dual
  UNION ALL
  SELECT 7, 'x2', 'l3' FROM dual
  UNION ALL
  SELECT 8, 'x2', 'l3' FROM dual
  UNION ALL
  SELECT 9, 'x3', 'l4' FROM dual
  ),
  t1 AS
  (SELECT COUNT(subscriber) totalcount,
    line,
    MAX(subscriber) keep (dense_rank last
  ORDER BY line ) subscribers
  FROM t
  GROUP BY line
  ORDER BY 1 DESC
  )
SELECT subscribers,
  listagg(line
  ||' had '
  || totalcount
  || ' calls ', ',') within GROUP (
ORDER BY totalcount) AS lines
FROM t1
GROUP BY subscribers

the results

subscribers       lines
x1                 l1 had 3 calls, l2 had 4 calls
x2                 l3 had 2 calls
x3                 l4 had 1 calls

Upvotes: 0

Marmite Bomber
Marmite Bomber

Reputation: 21063

The subquery must GROUP BY on SUB_ID (not on LINE_FROM). This will provide the total calls of a subscriber and not the top line calls.

In other words move the join in the subquery and group and order by SUB_ID.

DISTINCT in the main query is too late, you will get no duplicates but less results.

Upvotes: 1

bbrumm
bbrumm

Reputation: 1352

Could you try adding the DISTINCT keyword to the SELECT query at the bottom?

Something like this:

with TEMPRESULT AS 
(
select * from 
(
  select CALLS.LINE_FROM , count(*) totalcount
  from CALLS
  where (((END_DATE_CALL-START_DATE_DATE)*24*60*60)<=60 and to_char(S_DATE,'YYYY-MM-DD')='2015-12-12') 
  group by CALLS.LINE_FROM
  order by totalcount DESC
) 
where rownum <= 3
) 
select DISTINCT F_NAME,L_NAME
from TEMPRESULT
inner join SUBSCRIBERS_LINES on TEMPRESULT.LINE_FROM = SUBSCRIBERS_LINES.line_id
inner join SUBSCRIBERS on SUBSCRIBERS_LINES.SUB_ID = SUBSCRIBERS.SUB_ID;

In theory (I haven't tested it by creating this database) this should show:

X1

X2

X3

Upvotes: 0

Related Questions