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
thequery. 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