CODEWITHSUNDEEP
sqlpostgresql
Jason Smith
Jason Smith

Reputation: 127

Different values for a column

I have lots of single Addresses that have different Sq_ft values in them (this should not be the case). I want to parse through the columns that have different values for Sq_Ft for the same address. What can I add to the where clause which would assist me in doing this? I have tried adding the distinct syntax with Sq_ft but futile.

Example:

Address of New Home                      Sq_Ft
1234 Obama Drive                         2,578
1234 Obama Drive                         2,586

select "Address_of_New_Home","Sq_Ft"
from "Amazon".salesdatapcr
where "Sq_Ft" <>
order by 1

Upvotes: 0

Views: 30

Answers (2)

krokodilko
krokodilko

Reputation: 36107

Use EXISTS operator and a correlated subquery:

select "Address_of_New_Home","Sq_Ft"
from "Amazon".salesdatapcr t1
where EXISTS (
   SELECT null from "Amazon".salesdatapcr t2
   WHERE t1."Address_of_New_Home" = t2."Address_of_New_Home"
     and t1."Sq_Ft" <> t2."Sq_Ft"
)
order by 1

Upvotes: 3

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

This would list rows from a table for which there are more than one occurences of an address with different sq_ft:

SELECT salesdt.*
FROM "Amazon".salesdatapcr salesdt
JOIN (
  SELECT
    "Address_of_New_Home" AS home_address
  FROM
    "Amazon".salesdatapcr
  GROUP BY 1
  HAVING COUNT(DISTINCT "Sq_Ft") > 1
  ) multisqft ON
    salesdt."Address_of_New_Home" = multisqft.home_address
ORDER BY multisqft.home_address

You could just as well use EXISTS or IN to achieve this. ORDER is there to make things clearer.

Upvotes: 2

Related Questions