Re: simple query question

On Mon, Feb 07, 2005 at 07:23:20PM +0700, Akbar wrote:
>
> What query command that I have to issue so that I get the list
> like this:
> name sum buying_price sale_price
> mentos 13 110 140
> durex 9 200 210
> queen 10 400 450
>
> so this time, there is only one mentos. This mentos has 13 ( 8 + 5 )
> stuff, and use the highest index's ( that is 2 because 2 is higher than
> 1 ) buying_price and sale_price value.

You can get each name's sum and highest index with an aggregate:

SELECT name, sum(sum), max(index) AS index
FROM view_stok_table_total
GROUP BY name;

name | sum | index
--------+-----+-------
mentos | 13 | 2
queen | 10 | 4
durex | 9 | 3
(3 rows)

You could then join those results with the view to get the price
columns for each index:

SELECT ag.name, ag.sum, v.buying_price, v.sale_price
FROM view_stok_table_total AS v
JOIN (SELECT name, sum(sum), max(index) AS index
FROM view_stok_table_total
GROUP BY name) AS ag USING (index)
ORDER BY index;

name | sum | buying_price | sale_price
--------+-----+--------------+------------
mentos | 13 | 110 | 140
durex | 9 | 200 | 210
queen | 10 | 400 | 450
(3 rows)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/