Обсуждение: 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