aggregation question

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))

Responses

Browse pgsql-sql by date

  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