Thread: simple query question
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
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/
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
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/