pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMore wacky grouping
4+ messages / 4 participants
[nested] [flat]
* More wacky grouping
@ 2003-04-04 00:29 James Taylor <jtx@hatesville.com>
2003-04-04 00:42 ` Re: More wacky grouping Josh Berkus <josh@agliodbs.com>
2003-04-04 05:58 ` Re: More wacky grouping Joe Conway <mail@joeconway.com>
2003-04-04 07:20 ` Re: More wacky grouping Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 3 replies; 4+ messages in thread
From: James Taylor @ 2003-04-04 00:29 UTC (permalink / raw)
To: pgsql-sql
I need to do grouping based on various aggregates, but the way my table
is set up (it can't change) is making things kind of difficult.
My table looks something like this:
Id | id_customer | id_datatype | content
----------------------------------------
1 | 25 | 1 | John
2 | 25 | 2 | Globochem
3 | 25 | 3 | 845.92
4 | 26 | 1 | Patty
5 | 26 | 2 | Globochem
6 | 26 | 3 | 284.92
7 | 27 | 1 | Sam
8 | 27 | 2 | Toxichem
9 | 27 | 3 | 1239.47
Datatype 1 = Name, 2 = Company, 3 = account balance.
I need to do an aggregate function on content where id_datatype = 3, and
then group by content where id_datatype = 2
So, say I wanted to do a sum function, it would end up with something
like:
Company | Sum
-------------------
Globochem | 1130.84
Toxichem | 1239.47
The content isn't static, so I can't use static names for content in the
query.
Any ideas on this one?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: More wacky grouping
2003-04-04 00:29 More wacky grouping James Taylor <jtx@hatesville.com>
@ 2003-04-04 00:42 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2003-04-04 00:42 UTC (permalink / raw)
To: James Taylor <jtx@hatesville.com>; pgsql-sql
James,
> I need to do grouping based on various aggregates, but the way my table
> is set up (it can't change) is making things kind of difficult.
Yeah. You should track down the previous database designer and break his
fingers ....
Anyway, you'll need to work through subselects. That is, subselect each
datatype from the table as if it were a seperate table and join them, e.g.
FROM (SELECT content, id_customer FROM table1 WHERE id_datatype = 2) tb2,
(SELECT content, id_customer FROM table1 WHERE id_datatype = 3) tb3
etc.
Also, since you'll be summing on a text field which is being converted to
NUMERIC on the fly, expect your query to be slow as molasses.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: More wacky grouping
2003-04-04 00:29 More wacky grouping James Taylor <jtx@hatesville.com>
@ 2003-04-04 05:58 ` Joe Conway <mail@joeconway.com>
2 siblings, 0 replies; 4+ messages in thread
From: Joe Conway @ 2003-04-04 05:58 UTC (permalink / raw)
To: James Taylor <jtx@hatesville.com>; +Cc: pgsql-sql
James Taylor wrote:
> So, say I wanted to do a sum function, it would end up with something
> like:
>
> Company | Sum
> -------------------
> Globochem | 1130.84
> Toxichem | 1239.47
>
Same crosstab function mentioned the other day:
select company, sum(acct_bal) as "sum" from (select name, company,
acct_bal from crosstab('select id_customer, id_datatype, content from
mytable order by 1','select distinct id_datatype from mytable') as
(id_customer int, name text, company text, acct_bal numeric)) as ss
group by company;
company | sum
-----------+---------
Toxichem | 1239.47
Globochem | 1130.84
(2 rows)
HTH,
Joe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: More wacky grouping
2003-04-04 00:29 More wacky grouping James Taylor <jtx@hatesville.com>
@ 2003-04-04 07:20 ` Manfred Koizar <mkoi-pg@aon.at>
2 siblings, 0 replies; 4+ messages in thread
From: Manfred Koizar @ 2003-04-04 07:20 UTC (permalink / raw)
To: James Taylor <jtx@hatesville.com>; +Cc: pgsql-sql
On Thu, 3 Apr 2003 16:29:08 -0800, "James Taylor" <jtx@hatesville.com>
wrote:
>My table looks something like this:
>
>Id | id_customer | id_datatype | content
>----------------------------------------
>1 | 25 | 1 | John
>2 | 25 | 2 | Globochem
>3 | 25 | 3 | 845.92
>[...]
>Datatype 1 = Name, 2 = Company, 3 = account balance.
>
>I need to do an aggregate function on content where id_datatype = 3, and
>then group by content where id_datatype = 2
SELECT c.content AS "Company", sum(b.content::float) AS "Sum"
FROM james AS c LEFT JOIN james AS b
ON (c.id_customer = b.id_customer AND b.id_datatype = 3)
WHERE c.id_datatype = 2
GROUP BY c.content;
should work, if there is exactly one row with id_datatype = 2 for each
id_customer.
Servus
Manfred
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-04-04 07:20 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-04 00:29 More wacky grouping James Taylor <jtx@hatesville.com>
2003-04-04 00:42 ` Josh Berkus <josh@agliodbs.com>
2003-04-04 05:58 ` Joe Conway <mail@joeconway.com>
2003-04-04 07:20 ` Manfred Koizar <mkoi-pg@aon.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox