How to get ride of the duplicate rows.



Hello Experts,

Table A

c1                        c2                   c3                             c4                             c5            c6

s11                      z21                 XX         28-dec-2010 10:00:00              5               342
s11                      z21                 YY         28-dec-2010 10:50:00              5                342
s12                      z22                 XX         28-dec-2010 11:00:00              3                342
s12                      z22                 YY         28-dec-2010 12:30:00              3                342
s13                      z23                 XX         28-dec-2010 10:05:00              15              340
s13                      z23                 YY         29-dec-2010 10:00:00              15              340
s14                      z24                 XX         28-dec-2010 10:05:00              1                341
s14                      z24                 YY         28-dec-2010 10:05:00              1                 341


Table B

cul1                   cul2

340                   ABC
341                   ABC
342                   ABC

with query1 as ( select * from tableA where c3 = 'XX' and c4 between sysdate -1 and sysdate and                      c5 > 0 ),
query2 as ( select * from tableA where c3 = 'YY' and c4 between sysdate -1 and sysdate and                      c5 > 0 )
select q3.cul2, q3.cul1, nvl(sum(case  when c5 = 0 then 0 else 1 end)  
    from tableB q3, query1, query2  w
   where q3.cul1 = query1.c6  and  q3.cul1 = query2.c6 and query1.c1 = query2.c1 and
         query1.c2 = query2.c2 and ( (query2.c4 - query1.c4) * 24) < 4
   group by q3.cul2, q3.cul1;

This query supposed to print like:

ABC        341            1
ABC        342             2

But the above query brings

ABC        341            2
ABC        342             4

Please help me to avoid the duplication row calculation.

Thanks in advance.
nbuserAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

simonpaul64Commented:
Your XX and YY rows are being rolled together, so if you want them counted apart you need to include the c3 column from  table A in your group by.
awking00Information Technology SpecialistCommented:
Try the attached.
query.txt
Naveen KumarProduction Manager / Application Support ManagerCommented:
I have setup the tables with your sample data.. but i am getting syntax errors for your query.

with query1 as ( select * from tableA where c3 = 'XX' and c4 between sysdate -1 and sysdate and                      c5 > 0 ),
query2 as ( select * from tableA where c3 = 'YY' and c4 between sysdate -1 and sysdate and                      c5 > 0 )
select q3.cul2, q3.cul1, nvl(sum(case  when c5 = 0 then 0 else 1 end)  
    from tableB q3, query1, query2  w
   where q3.cul1 = query1.c6  and  q3.cul1 = query2.c6 and query1.c1 = query2.c1 and
         query1.c2 = query2.c2 and ( (query2.c4 - query1.c4) * 24) < 4
   group by q3.cul2, q3.cul1;

1) one error for NVL() function because the second argument is missing
2) query2 has been aliased with w but in the where clause you are saying query2.c6, query2.c1, query2.c2, query2.c4 in 4 places.

Can you provide me the correct query which gave you that output, so that it can be modified as per your requirement.

Thanks

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nbuserAuthor Commented:
I haven't got the correct solution for this question.  Just gave a grade for closing purpose.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.