From: Luiz K. Matsumura <luiz@planit.com.br>
To: pgsql-sql@postgresql.org
Subject: Aggregate question (Sum)
Date: Mon, 19 Nov 2007 18:46:50 -0200
Message-ID: <4741F63A.6070500@planit.com.br> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: luiz@planit.com.br
Subject: Re: Aggregate question (Sum)
In-Reply-To: <4741F63A.6070500@planit.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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