From: | Samuel Gendler <sgendler(at)ideasculptor(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | aggregation question |
Date: | 2010-12-01 03:47:29 |
Message-ID: | AANLkTim9uMsXB3cNC_SXYLYgeDKCps2X5vNZGgguYRz9@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I have a fact table with a number of foreign keys to dimension data and some
measure columns. Let's assume I've got dimension columns a,b,c,d,e, and f
and measure columns x, and y.
I need to be able to find the value of f, x, and y for the row with min(x/y)
when e in (1,2) and max(x/y) when e not in (1,2) with grouping by a,b,c, and
d.
A query which would identify the row in question would look like this:
select min(x/y), a, b, c, d, e from my_table group by a,b,c,d,e order by
a,b,c,d
But I don't know how to go about also extracting the value of x, y, and f
for the row which is matched - I suppose I could join to a subquery, but
that strikes me as probably not being the most efficient way to accomplish
it.
Actually, it occurs to me that I've potentially got more than one row for
any given a,b,c,d,e,f tuple, but I want a min for all rows aggregated by f
Basically, for each (a,b,c,d,e) I want to return sum(x), sum(y), and f (as
well as a,b,c,d,e) for the f with min(sum(x)/sum(y))
From | Date | Subject | |
---|---|---|---|
Next Message | John Fabiani | 2010-12-01 06:06:58 | OT - load a shp file |
Previous Message | Jeff Bland | 2010-11-30 22:16:53 | DELETE WHERE EXISTS unexpected results |