Thread: simple query question

simple query question

From
Akbar
Date:
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


Re: simple query question

From
Michael Fuhr
Date:
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/

Re: simple query question

From
"John K. Herreshoff"
Date:
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

Re: simple query question

From
Michael Fuhr
Date:
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/