Group by with insensitive order

On Wed, Jan 19, 2011 at 03:37:58PM -0500, Aram Fingal wrote:

Suppose I'm doing a group by query like the following:

SELECT drug1, drug2, AVG(response)
FROM data
GROUP BY drug1, drug2

The problem is that the same drug may appear sometimes as drug1 and
sometimes as drug2. So, for example, the combination "aspirin,
acetaminophen" may also appear as "acetaminophen, aspirin" and I want
these grouped together in the query. Is there a simple way to do
this?

instead of drug1 write: least(drug1, drug2) and instead of drug2 write:
greatest(drug1, drug2) - both in select and group by.

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Aram Fingal <fingal@multifactorial.com> writes:

Suppose I'm doing a group by query like the following:
SELECT drug1, drug2, AVG(response)
FROM data
GROUP BY drug1, drug2

The problem is that the same drug may appear sometimes as drug1 and sometimes as drug2. So, for example, the combination "aspirin, acetaminophen" may also appear as "acetaminophen, aspirin" and I want these grouped together in the query. Is there a simple way to do this?

Easy way is something like

SELECT LEAST(drug1, drug2), GREATEST(drug1, drug2), AVG(response)
FROM data
GROUP BY 1, 2

though it'd be a PITA to scale that to more than 2 drugs.

regards, tom lane

Easy way is something like

SELECT LEAST(drug1, drug2), GREATEST(drug1, drug2), AVG(response)
FROM data
GROUP BY 1, 2

though it'd be a PITA to scale that to more than 2 drugs.

regards, tom lane

Thanks, Tom and Hubert, who said the same thing. For the foreseeable future, two drug combos are all we're going to be doing. That's bad enough with over 8000 combos of two.

-Aram