Thread: More wacky grouping

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?

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 BerkusAglio Database SolutionsSan Francisco

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

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 = 2GROUP BY c.content;
 

should work, if there is exactly one row with id_datatype = 2 for each
id_customer.

ServusManfred