Postgresql: Count rows with same value but in different order

Question

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,

For instance, we count a row : Coke | Lemon | 3 . Then we must not count again Lemon | Coke | 3 , just because Lemon changed places with Coke. So applying this idea, considering this table, the result will be 5. Do you know how can I do it using SQL ? Thanks, — Samuel Donadelli
– Samuel Donadelli, Commented Feb 2, 2014 at 20:55
You didn't answer Houari's question: 5 what? If you meant you want to count the number of distinct products, then (a) there are 6 of them, and (b) why not say that? — j_random_hacker
– j_random_hacker, Commented Feb 2, 2014 at 21:33
@j_random_hacker: Distinct unordered pairs, i.e. distinct (product, product1) pairs where (a,b) and (b,a) are considered to be the same. — mu is too short
– mu is too short, Commented Feb 2, 2014 at 21:36

mu is too short · Accepted Answer · 2014-02-02 21:33:47Z

4

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

answered Feb 2, 2014 at 21:33

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:56:06Z

2

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

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Feb 2, 2014 at 21:32

Houari

5,6513 gold badges33 silver badges57 bronze badges

1 Comment

mu is too short Over a year ago

This nicely generalizes to more than two product columns where the greatest/least trick doesn't work: sqlfiddle.com/#!15/518b4/1

Collectives™ on Stack Overflow

Postgresql: Count rows with same value but in different order

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related