Обсуждение: Aggregate question (Sum)

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.002 | 2007-01-01 |           1 |   30.00

order_item
fk_order |  qty  | fk_product
--------------------------------1       |    5  |           A1       |    2  |           B2       |    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(
DISTINCTorder.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.

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.

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.