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

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