Thread: sum(field) as total group with a where condition...

Am trying to accomplish a search within pgsql (from PHP though not a PHP issue
other than I need one command line)

SELECT field1,field2,sum(field3) AS total FROM table WHERE field2=12345 GROUP BY
field1

field1 field2 field3
------ ------ ------
55555  12344  10
55555  12345  10
55555  12345  10
44444  12345  10
33333  12345  10
66666  12346  10
77777  12346  10
88888  12347  10

and want it to find all those that match a particular date (field2), then return
sums for each field1...  using the above table should generate the following
result for field2=12345

55555 12345 20
44444 12345 10
33333 12345 10

It is hacking on the query, and again, I am calling this from a PHP script so it
would be easiers if it can be accomplished in one select query for the return
result.

Appreciate any insight.

Dave

On Sun, 9 Sep 2001, Dave VanAuken wrote:

> Am trying to accomplish a search within pgsql (from PHP though not a PHP issue
> other than I need one command line)
>
> SELECT field1,field2,sum(field3) AS total FROM table WHERE field2=12345 GROUP BY
> field1
>
>
> field1 field2 field3
> ------ ------ ------
> 55555  12344  10
> 55555  12345  10
> 55555  12345  10
> 44444  12345  10
> 33333  12345  10
> 66666  12346  10
> 77777  12346  10
> 88888  12347  10
>
> and want it to find all those that match a particular date (field2), then return
> sums for each field1...  using the above table should generate the following
> result for field2=12345
>
> 55555 12345 20
> 44444 12345 10
> 33333 12345 10
>
> It is hacking on the query, and again, I am calling this from a PHP script so it
> would be easiers if it can be accomplished in one select query for the return
> result.

Unless I'm missing something, wouldn't
select field1, field2, sum(field3) as total from table where field2=12345
 group by field1, field2;
do what you wanted?  You need to group by both because they're used in the
select list not in an aggregate function, but...

>> and want it to find all those that match a particular date (field2),
>then return
>> sums for each field1...  using the above table should generate the following
>> result for field2=12345
>
>select
>    field1,field2,sum(field3) as total
>from
>    table
>where
>    field2=12345
>group by
>    field1, field2
>;

forgot to group by both fields that were not in the sum function...
beautiful...  thanks to those who responded.

Dave