pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: max and sum function
3+ messages / 3 participants
[nested] [flat]

* Re: max and sum function
@ 2003-03-23 03:47  ryanne cruz <ryanne.cruz@up.edu.ph>
  0 siblings, 1 reply; 3+ messages in thread

From: ryanne cruz @ 2003-03-23 03:47 UTC (permalink / raw)
  To: pgsql-php

hi list. 
 
does anyone here knows of a function that is similar to the sum function but 
involves more than one column? for example, i have a table with columns id, 
col1, col2 and col3. i want to get the max value from the three columns gruped 
by an id. 
 
what i came up with is something like this: 
 
max1=select max(one) from (select sum(col1) as one from table group by id); 
max2=select max(two) from (select sum(col2) as two from table group by id); 
max3=select max(three) from (select sum(col3) as three from table group by 
id); 
 
then i compare the 3 max values and get the maximum of the 3.  
 
is there an easier way to do this? 
 
thanks. 
 
ryanne  
 




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: max and sum function
@ 2003-03-24 06:09  Waruna Geekiyanage <waruna@nirmani.com>
  parent: ryanne cruz <ryanne.cruz@up.edu.ph>
  0 siblings, 1 reply; 3+ messages in thread

From: Waruna Geekiyanage @ 2003-03-24 06:09 UTC (permalink / raw)
  To: pgsql-php


SELECT MAX(aa.res) from ((select sum(col1) as res from table group by id)
UNION
(select sum(col2) as res from table group by id)
UNION
(select sum(col3) as res from table group by id)) AS aa;
----- Original Message -----
From: "ryanne cruz" <ryanne.cruz@up.edu.ph>
To: <pgsql-php@postgresql.org>
Sent: Sunday, 23 March, 2003 9:47 AM
Subject: Re: [PHP] max and sum function


hi list.

does anyone here knows of a function that is similar to the sum function but
involves more than one column? for example, i have a table with columns id,
col1, col2 and col3. i want to get the max value from the three columns
gruped
by an id.

what i came up with is something like this:

max1=select max(one) from (select sum(col1) as one from table group by id);
max2=select max(two) from (select sum(col2) as two from table group by id);
max3=select max(three) from (select sum(col3) as three from table group by
id);


then i compare the 3 max values and get the maximum of the 3.

is there an easier way to do this?

thanks.

ryanne



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: max and sum function
@ 2003-03-26 14:26  Greg Stark <gsstark@mit.edu>
  parent: Waruna Geekiyanage <waruna@nirmani.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Greg Stark @ 2003-03-26 14:26 UTC (permalink / raw)
  To: Waruna Geekiyanage <waruna@nirmani.com>; +Cc: pgsql-php

"Waruna Geekiyanage" <waruna@nirmani.com> writes:

> does anyone here knows of a function that is similar to the sum function but
> involves more than one column? for example, i have a table with columns id,
> col1, col2 and col3. i want to get the max value from the three columns
> gruped
> by an id.
> 
> what i came up with is something like this:
> 
> max1=select max(one) from (select sum(col1) as one from table group by id);
> max2=select max(two) from (select sum(col2) as two from table group by id);
> max3=select max(three) from (select sum(col3) as three from table group by
> id);

select int4larger(int4larger(max(one),max(two)),max(three)) from table group by id;

I'm a bit stumped why it seems I have to use "int4larger" and just "larger"
doesn't work like with most functions. There must be a right way to do it
without hard coding the datatype.

-- 
greg




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2003-03-26 14:26 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-23 03:47 Re: max and sum function ryanne cruz <ryanne.cruz@up.edu.ph>
2003-03-24 06:09 ` Waruna Geekiyanage <waruna@nirmani.com>
2003-03-26 14:26   ` Greg Stark <gsstark@mit.edu>

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