Hi.....

Imagine I have view named view_stok_table_total....
SELECT * FROM view_stok_table_total;
index name sum buying_price sale_price
1 mentos 5 100 120
2 mentos 8 110 140
3 durex 9 200 210
4 queen 10 400 450

You see, there are two mentos ( same product but different
price )......
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.

Thank you....

regards,

akbar


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Monday 07 February 2005 07:23 am, Akbar wrote:

Try SELECT name, sum(sum) as total_sum, buying_price, sale_price
FROM view_stok_table_total
GROUP BY name, buying_price, sale_price
ORDER BY buying_price, sale_price;

HTH.

John.

> Hi.....
>
> Imagine I have view named view_stok_table_total....
> SELECT * FROM view_stok_table_total;
> index name sum buying_price sale_price
> 1 mentos 5 100 120
> 2 mentos 8 110 140
> 3 durex 9 200 210
> 4 queen 10 400 450
>
> You see, there are two mentos ( same product but different
> price )......
> 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.
>
> Thank you....
>
> regards,
>
> akbar
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Mon, Feb 07, 2005 at 06:17:47AM -0500, John K. Herreshoff wrote:
>
> Try SELECT name, sum(sum) as total_sum, buying_price, sale_price
> FROM view_stok_table_total
> GROUP BY name, buying_price, sale_price
> ORDER BY buying_price, sale_price;

To calculate the total sum for each name, you need to use "GROUP BY name",
not "GROUP BY name, buying_price, sale_price". For the sample data the
above query gives the following result, which is not what was requested:

name | total_sum | buying_price | sale_price
--------+-----------+--------------+------------
mentos | 5 | 100 | 120
mentos | 8 | 110 | 140
durex | 9 | 200 | 210
queen | 10 | 400 | 450
(4 rows)

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match