Oracle query

I am trying to get duplicates in the query. I do have the query.

select
unit_number,u.chk_digit, item_id,
count(*)
from invoice_detail id, invoice_header ih, units@plab.world u
where id.invoice_number = ih.invoice_number
and id.unit_number = u.unit_id
and debit_credit = 'DB'
and unit_number is not null
and id.unit_number in ('W039713779596','W039714958552')
and invoice_date between '01-jan-2014' and trunc(sysdate-1)
group by
unit_number,u.chk_digit, item_id
having count(*) > 1
order by 3, 4

But I am looking for a query to get duplicates of all debits where count of credits is greater than count of debits for the unit number and item id(product code) combination.


select count(debit_credit)
from invoice_detail
where unit_number in ('W039714958552')
and debit_credit = 'CR'
group by
unit_number, item_id   (3)

select count(debit_credit)  
from invoice_detail
where unit_number in ('W039714958552')
and debit_credit = 'DB'
group by
unit_number, item_id  ( 2)


select count(debit_credit)
from invoice_detail
where unit_number in ('W039713779596')
and debit_credit = 'CR'
group by
unit_number, item_id ( 4 )

select count(debit_credit)  
from invoice_detail
where unit_number in ('W039713779596')
and debit_credit = 'DB'
group by
unit_number, item_id  ( 7 )


In the screen attached I should only get the results for unit number W039714958552

Here count(credits) > count(debits) i.e   3 > 2

expected results
Unit_number       chk_digit  item_id  count(*)

W039714958552      8             E3056        2

Appreciate for the help

 query results
LVL 6
anumosesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PaulConnect With a Mentor Commented:
Instead of using SUM() when you want a count, simply return NULL from the decode.

SELECT
      unit_number
    , item_id
    , COUNT(decode(debit_credit, 'DB', 1, NULL)) CNT_DEBIT
    , COUNT(decode(debit_credit, 'CR', 1, NULL)) CNT_CREDIT
FROM INVOICE_DETAIL
GROUP BY unit_number
       , item_id
HAVING COUNT(decode(debit_credit, 'CR', 1, NULL)) > COUNT(decode(debit_credit, 'DB', 1, NULL))
;

Open in new window

OR

I prefer to use CASE EXPRESSIONS, and when using COUNT() don't cater for the else condition as this becomes NULL which is what we require.
SELECT
      unit_number
    , item_id
    , COUNT(case when debit_credit = 'DB' then 1 end) CNT_DEBIT
    , COUNT(case when debit_credit = 'CR' then 1 end) CNT_CREDIT
FROM INVOICE_DETAIL
GROUP BY unit_number
       , item_id
HAVING COUNT(case when debit_credit = 'CR' then 1 end) > COUNT(case when debit_credit = 'DB' then 1 end)
;

Open in new window

see: http://sqlfiddle.com/#!4/b2e55/2

    CREATE TABLE INVOICE_DETAIL
    	(UNIT_NUMBER varchar2(13), ITEM_ID varchar2(5), DEBIT_CREDIT varchar2(2), OTHER int)
    //
    
    INSERT ALL 
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039713779596', 'E0896', 'CR', 12)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039713779596', 'E0896', 'CR', 13)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039713779596', 'E0896', 'CR', 15)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039713779596', 'E0896', 'DB', 11)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039713779596', 'E0896', 'DB', 14)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039714958552', 'E3056', 'CR', 23)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039714958552', 'E3056', 'CR', 24)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039714958552', 'E3056', 'DB', 21)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039714958552', 'E3056', 'DB', 22)
    SELECT * FROM dual
    //

**Query 1**:

    SELECT
          unit_number
        , item_id
        , COUNT(decode(debit_credit, 'DB', 1, NULL)) CNT_DEBIT
        , COUNT(decode(debit_credit, 'CR', 1, NULL)) CNT_CREDIT
    FROM INVOICE_DETAIL
    GROUP BY unit_number
           , item_id
    HAVING COUNT(decode(debit_credit, 'CR', 1, NULL)) > COUNT(decode(debit_credit, 'DB', 1, NULL))
    

**[Results][2]**:
    
    |   UNIT_NUMBER | ITEM_ID | CNT_DEBIT | CNT_CREDIT |
    |---------------|---------|-----------|------------|
    | W039713779596 |   E0896 |         2 |          3 |


**Query 2**:

    SELECT
          unit_number
        , item_id
        , COUNT(case when debit_credit = 'DB' then 1 end) CNT_DEBIT
        , COUNT(case when debit_credit = 'CR' then 1 end) CNT_CREDIT
    FROM INVOICE_DETAIL
    GROUP BY unit_number
           , item_id
    HAVING COUNT(case when debit_credit = 'CR' then 1 end) > COUNT(case when debit_credit = 'DB' then 1 end)
    

**[Results][3]**:
    
    |   UNIT_NUMBER | ITEM_ID | CNT_DEBIT | CNT_CREDIT |
    |---------------|---------|-----------|------------|
    | W039713779596 |   E0896 |         2 |          3 |



  [1]: http://sqlfiddle.com/#!4/b2e55/2

Open in new window

0
 
HainKurtSr. System AnalystCommented:
this maybe?

select unit_number, item_id, count(credits) c_credit, count(debits)  c_debit
from invoice_detail
group by 
unit_number, item_id
having count(credits) > count(debits)

Open in new window

0
 
anumosesAuthor Commented:
invoice_detail has one column debit_credit and data is CR for credits and DB for debits.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
here it is:

with invoice_detail as (
select 'W039713779596' unit_number, 'E0896' item_id, 'DB' debit_credit, '11' other from dual
union select 'W039713779596', 'E0896', 'CR', '12' from dual
union select 'W039713779596', 'E0896', 'CR', '13' from dual
union select 'W039713779596', 'E0896', 'DB', '14' from dual
union select 'W039713779596', 'E0896', 'CR', '15' from dual
union select 'W039714958552', 'E3056', 'DB', '21' from dual
union select 'W039714958552', 'E3056', 'DB', '22' from dual
union select 'W039714958552', 'E3056', 'CR', '23' from dual
union select 'W039714958552', 'E3056', 'CR', '24' from dual
)
select unit_number, item_id, sum(decode(debit_credit,'DB',1,0)) cnt_debit, sum(decode(debit_credit,'CR',1,0)) cnt_credit
from invoice_detail
group by unit_number, item_id
having sum(decode(debit_credit,'DB',1,0)) < sum(decode(debit_credit,'CR',1,0))

UNIT_NUMBER	ITEM_ID	CNT_DEBIT	CNT_CREDIT
W039713779596	E0896	2	3

Open in new window

0
 
anumosesAuthor Commented:
Nice solutions. I need one more information. How can I exclude
COUNT(decode(debit_credit, 'DB', 1, NULL)) CNT_DEBIT when it is '0'
0
 
anumosesAuthor Commented:
0
 
HainKurtSr. System AnalystCommented:
add

and COUNT(decode(debit_credit, 'DB', 1, NULL)) !=0

at the end of query...

with invoice_detail as (
select 'W039713779596' unit_number, 'E0896' item_id, 'DB' debit_credit, '11' other from dual
union select 'W039713779596', 'E0896', 'CR', '12' from dual
union select 'W039713779596', 'E0896', 'CR', '13' from dual
union select 'W039713779596', 'E0896', 'DB', '14' from dual
union select 'W039713779596', 'E0896', 'CR', '15' from dual
union select 'W039714958552', 'E3056', 'DB', '21' from dual
union select 'W039714958552', 'E3056', 'DB', '22' from dual
union select 'W039714958552', 'E3056', 'CR', '23' from dual
union select 'W039714958552', 'E3056', 'CR', '24' from dual
)
select unit_number, item_id, sum(decode(debit_credit,'DB',1,0)) cnt_debit, sum(decode(debit_credit,'CR',1,0)) cnt_credit
from invoice_detail
group by unit_number, item_id
having sum(decode(debit_credit,'DB',1,0)) < sum(decode(debit_credit,'CR',1,0)) and COUNT(decode(debit_credit, 'DB', 1, NULL)) !=0

Open in new window

0
 
anumosesAuthor Commented:
group function cannot be used in where clause. It is giving error.

ORA-00934: group function is not allowed here
0
 
HainKurtSr. System AnalystCommented:
not sure what you did, but the last post with added filter is working fine...
post the query you have
0
 
anumosesAuthor Commented:
SELECT
      id.unit_number
    , item_id
      , COUNT(decode(debit_credit, 'DB', 1, NULL)) CNT_DEBIT
    , COUNT(decode(debit_credit, 'CR', 1, NULL)) CNT_CREDIT
FROM INVOICE_DETAIL id, invoice_header ih, units@plab.world u
where id.invoice_number = ih.invoice_number
and id.unit_number is not null
and id.unit_number = u.unit_id
and invoice_date between '01-jan-2014' and trunc(sysdate-1)
and COUNT(decode(debit_credit, 'DB', 1, NULL)) !=0
GROUP BY id.unit_number
       , item_id
HAVING COUNT(decode(debit_credit, 'CR', 1, NULL)) > COUNT(decode(debit_credit, 'DB', 1, NULL))
0
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
and COUNT(decode(debit_credit, 'DB', 1, NULL)) !=0

>>> move it to end
0
 
PaulConnect With a Mentor Commented:
:)

the WHERE clause does not understand aggregation functions such as COUNT() (so you get an error)

the HAVING clause is designed to handle those functions, so if you want to filter results using an aggregate function such as COUNT() or SUM() etc. use those in the HAVING clause.
0
 
anumosesAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.