CODEWITHSUNDEEP
sqlpostgresqlselectcount
Gianna
Gianna

Reputation: 13

SQL Count elements in pairs

I have a simple table

CREATE TABLE aaa AS
(
  ogid integer NOT NULL,
  ocolor character varying(80) NOT NULL,
  vgid integer NOT NULL,
  vcolor character varying(80) NOT NULL,
)

aa contains some values, like in this example

Ogid OColor Vgid VColor
1   v       1   v
1   v       5   r
1   v       8   g
2   r       5   r
3   g       7   r
4   g       5   r
5   r       7   r
5   r       9   g
6   g       6   g

I need to count the number of elements in every couple (OColor, VColor) grouping together simmetric couples ( for example the elements of g-r and r-g go together ).

I need a result similar to:

OColor Vcolor nelement
b      b      10
b      g      16
g      v      2

Upvotes: 1

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

If I understand correctly, you can just use least() and greatest():

select least(ocolor, vcolor), greatest(ocolor, vcolor), count(*)
from table t
group by least(ocolor, vcolor), greatest(ocolor, vcolor);

However, your question is confusing because the results seem to have nothing to do with the sample data.

Upvotes: 1

Related Questions