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/