CODEWITHSUNDEEP
sqlpostgresql
Samuel Donadelli
Samuel Donadelli

Reputation: 347

Postgresql: Count rows with same value but in different order

I am trying to count rows like in this table: 

Product | Product2 | Value
Coke    | Lemon    |   3
Coke    | Potato   |   4
Coke    | Seven Up |   10
Lemon   | Coke     |   3
Lemon   | Meat     |   4
Pancake | Meat     |   23
Potato  | Coke     |   4
Seven Up| Coke     |   10
Meat    | Lemon    |   4
Meat    | Pancake  |   23

I must count the items but if they change order I still must count just once. So in this case I would have a result: 5. By the way, I already tried "where Product!= Product2" but this is not enough.

Can someone tell me what to do in this case? Thanks,

Upvotes: 0

Views: 1888

Answers (2)

mu is too short
mu is too short

Reputation: 434765

Since you only have two columns, you can use greatest and least to ignore the column order:

select greatest(product, product2) as a,
       least(product, product2) as b
from t
order by a, b

will give you:

Lemon    | Coke
Lemon    | Coke
Meat     | Lemon
Meat     | Lemon
Pancake  | Meat
Pancake  | Meat
Potato   | Coke
Potato   | Coke
Seven Up | Coke
Seven Up | Coke

Then toss in a distinct and count:

select count(
  distinct (
    greatest(product, product2),
    least(product, product2)
  )
)
from t

Demo: http://sqlfiddle.com/#!15/32111/6

Upvotes: 4

Houari
Houari

Reputation: 5641

Based on This array sorting function, we can convert rows to an arrays, sort the array, and than select distinct values:

CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(SELECT unnest($1) ORDER BY 1)
$$;

and than:

SELECT count( distinct array_sort(array[product, product2]  ) ) FROM your_table

Hope that helps

Upvotes: 2

Related Questions