CODEWITHSUNDEEP
sqloracle-database
Rally Cautiverio
Rally Cautiverio

Reputation: 19

Oracle SQL Query for displaying values?

I have a table as shown

Amount      Debit      Credit    Description
------      -----      ------    -----------
275.00      275.00               Payroll
500.00                 500.00    Payroll
288.00      288.00               Payroll
500.00      500.00               Payroll
600.00      600.00               Payroll
988.00                 988.00    Payroll
600.00                 600.00    Payroll

and I want to display two distinct numbers as shown below from the above mentioned table

Amount      Debit      Credit    Description
------      -----      ------    -----------
500.00                 500.00    Payroll
500.00      500.00               Payroll

and 

Amount      Debit      Credit    Description
------      -----      ------    -----------
600.00      600.00               Payroll
600.00                 600.00    Payroll

Now, the question is what would be the Oracle SQL for the this Query??

Upvotes: 1

Views: 166

Answers (4)

John Woo
John Woo

Reputation: 263893

try it using EXISTS. 

SELECT  a.*
FROM    tableName a
WHERE   EXISTS
        (
            SELECT  1
            FROM    tableName b
            WHERE   a.Amount = b.Amount 
            GROUP   BY Amount
            HAVING  COUNT(AMOUNT) = 2 AND
                    COUNT(Debit) = 1 AND
                    COUNT(Credit) = 1
        )

Upvotes: 1

user2295715
user2295715

Reputation: 62

Like this

select amount,debit,credit,Description 
from tablename 
where amount = 500 and amount = 600 
group by amount,debit,credit,description;

Upvotes: 0

DB_learner
DB_learner

Reputation: 1026

select * from table where amount in (select amount from table group by amount, description having sum(debit, credit) = 0 )

But its better to self join and group by with any ID column (I am sure that you will have in your original table) rather than amount. Because same amount may repeat with not equivalent debit or credit.

Upvotes: 0

TechDo
TechDo

Reputation: 18659

Please try:

SELECT
 Amount,
 Debit,
 Credit,
 Description
FROM
 YourTable
WHERE
 Amount IN (500.00, 600.00)
ORDER BY Amount

Upvotes: 0

Related Questions