CODEWITHSUNDEEP
sqlpostgresql
msagala25
msagala25

Reputation: 1806

Finding records in PostgreSQL

My problem is, how to find records in a single table where it don't have a same record. Well, that sounds hard to understand, I will show you in diagram my explanation.

Example you have this table:

tblsample

accountid|accountname|budgetforyear|
-----------------------------------
      30 |      rent |        2018 |
      40 |    lights |        2018 |
      50 |     water |        2018 | <---- How can I only query this?
      30 |      rent |        2017 |
      40 |    lights |        2017 |

How can you find that record/s ?

Upvotes: 1

Views: 30

Answers (2)

Nomikos Strigkos
Nomikos Strigkos

Reputation: 141

This is the solution I think

SELECT *
FROM ( SELECT accountid, accountname, count(budgetforyear) AS years
       FROM tblsample 
       GROUP BY accountid, accountname ) AS CountYears
WHERE CountYears.years = 1

Upvotes: 0

user330315
user330315

Reputation:

I think you want something like this:

select *
from sample t1
where not exists (select *
                  from sample t2
                  where (t2.accountname, t2.accountid) = (t1.accountname, t1.accountid) 
                    and t2.budgetforyear <> t1.budgetforyear);

The above returns rows from the table where no corresponding row exists with the same accountname and accountid and a different year.

Upvotes: 1

Related Questions