pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAggregate question (Sum)
3+ messages / 2 participants
[nested] [flat]
* Aggregate question (Sum)
@ 2007-11-19 20:46 Luiz K. Matsumura <luiz@planit.com.br>
2007-11-19 21:11 ` Re: Aggregate question (Sum) Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Luiz K. Matsumura @ 2007-11-19 20:46 UTC (permalink / raw)
To: pgsql-sql
Hi All,
I want to know if there are an easy manner to do an SQL like this bellow
where TotalOrdersValue sum the order.total just one time per order (as
count(DISTINCT order.id) do)
SELECT order.dtorder
, Count( DISTINCT order.fk_customer ) AS QtyCustomer
, Count( DISTINCT order.id ) AS QtyOrder
, Sum( order_item.qty ) AS TotalQtyItem
, Sum( order.total ) AS TotalOrders
FROM order JOIN order_item ON order_item.fk_order = order.id
GROUP BY 1
ORDER BY 1
Ex.
ORDER
Id | dtorder | fk_customer | total
-----------------------------------------
1 | 2007-01-01 | 1 | 100.00
2 | 2007-01-01 | 1 | 30.00
order_item
fk_order | qty | fk_product
--------------------------------
1 | 5 | A
1 | 2 | B
2 | 3 | C
The query acctualy returns (as expected):
dtorder | QtyCustomer | QtyOrder | TotalQtyItem | TotalOrders
------------------------------------------------------------------
2007-01-01 | 1 | 2 | 10 | 230.00
But I want
dtorder | QtyCustomer | QtyOrder | TotalQtyItem | TotalOrders
------------------------------------------------------------------
2007-01-01 | 1 | 2 | 10 | 130.00
I just want to avoid to do, if possible, things like:
SELECT totals.dtorder
, totals.QtyCustomer
, totals.QtyOrder
, totals.TotalQtyItem
, Sum( order.total ) AS TotalOrders
FROM ( SELECT order.dtorder
, Count( DISTINCT order.fk_customer ) AS QtyCustomer
, Count( DISTINCT order.id ) AS QtyOrder
, Sum( order_item.qty ) AS TotalQtyItem
FROM order JOIN order_item ON order_item.fk_order = order.id
GROUP BY 1 ) totals
JOIN order ON order.dtorder = totals.dtorder
GROUP BY 1,2,3,4
ORDER BY totals.dtorder
I say this because it's seem a waste of effort just to sum a value that
can be calculated on the same loop where postgresql will go on table
order...
If someone can give me some hint I will apreciate.
Tanks in advance.
--
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Aggregate question (Sum)
2007-11-19 20:46 Aggregate question (Sum) Luiz K. Matsumura <luiz@planit.com.br>
@ 2007-11-19 21:11 ` Rodrigo De León <rdeleonp@gmail.com>
2007-11-19 23:17 ` Re: Aggregate question (Sum) Luiz K. Matsumura <luiz@planit.com.br>
0 siblings, 1 reply; 3+ messages in thread
From: Rodrigo De León @ 2007-11-19 21:11 UTC (permalink / raw)
To: Luiz K. Matsumura <luiz@planit.com.br>; +Cc: pgsql-sql
On Nov 19, 2007 3:46 PM, Luiz K. Matsumura <luiz@planit.com.br> wrote:
> If someone can give me some hint I will apreciate.
This is more of a normalization problem. See:
http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
and lookup Third Normal Form.
You have a calculated total for each order, so I assume there's a
price for every product. You should join order_item to whichever table
has the individual cost for each product and multiply it by
order_item.qty.
Good luck.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Aggregate question (Sum)
2007-11-19 20:46 Aggregate question (Sum) Luiz K. Matsumura <luiz@planit.com.br>
2007-11-19 21:11 ` Re: Aggregate question (Sum) Rodrigo De León <rdeleonp@gmail.com>
@ 2007-11-19 23:17 ` Luiz K. Matsumura <luiz@planit.com.br>
0 siblings, 0 replies; 3+ messages in thread
From: Luiz K. Matsumura @ 2007-11-19 23:17 UTC (permalink / raw)
To: Rodrigo De León <rdeleonp@gmail.com>; +Cc: pgsql-sql
Hi Rodrigo, thanks for reply
Rodrigo De León wrote:
> On Nov 19, 2007 3:46 PM, Luiz K. Matsumura <luiz@planit.com.br> wrote:
>
>> If someone can give me some hint I will apreciate.
>>
>
> This is more of a normalization problem. See:
>
> http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
>
> and lookup Third Normal Form.
>
> You have a calculated total for each order, so I assume there's a
> price for every product. You should join order_item to whichever table
> has the individual cost for each product and multiply it by
> order_item.qty.
>
>
Ok, the example is a simplified scenario.
In some cases we have discount on order that can't be 'distributed' on
the itens.
For example:
Item Qty ValueUnity item_total (calculated)
1 5 1.01 5.05
2 6 1.01 6.06
The total for itens is 11.11 but we make a discount of 0.01 , then the
value of order is now 11.10
This 0.01 of discount can't be aplied on any "ValueUnity" because the
precision is 2 decimals.
We can have too other calculations over this values (like taxes) so in
my case isn't so simple to
determine the final value of the order just summing the value of the itens.
Anyway, thanks again...
--
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-11-19 23:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-19 20:46 Aggregate question (Sum) Luiz K. Matsumura <luiz@planit.com.br>
2007-11-19 21:11 ` Rodrigo De León <rdeleonp@gmail.com>
2007-11-19 23:17 ` Luiz K. Matsumura <luiz@planit.com.br>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox