Обсуждение: SUM the result of a subquery.

Поиск
Список
Период
Сортировка

SUM the result of a subquery.

От
negora
Дата:
<font face="Verdana">Hello:<br /><br /> I've a question related to the combination of the SUM aggregate function and
subqueries.Is it possible to SUM the resulting rows of a subquery? I'm explaining why I need this... I've a query like
this:</font><fontface="Verdana"><br /></font><blockquote><font face="Verdana">SELECT i.id_item, </font><font
face="Verdana">i.price, </font><font face="Verdana">SUM (o.quantity), </font><font face="Verdana">ROUND (SUM
(o.quantity)* i.price, 2) AS cost<br /> FROM orders o<br /> JOIN items i ON i.id_item = o.id_</font><font
face="Verdana">item</font><br/><font face="Verdana">WHERE o.date_order BETWEEN '2010-01-01' AND '2010-01-31'<br />
GROUPBY i.id_</font><font face="Verdana">item</font><font face="Verdana">, i.</font><font
face="Verdana">price</font><fontface="Verdana">;</font></blockquote><font face="Verdana"><br /> This just groups items
ofseveral orders by item ID, sums the quantities, multiplies such amounts by the price per unit, and rounds the result
to2 decimals. Very easy. The cost calculation is performed using the sum of the quantities instead of doing it per
line</font><fontface="Verdana"> to "lose" as less decimals as possible, because a rounding is applied on every
multiplication.<br/><br /> Now I need to get the total of ALL that lines in a separate query. It'd be really simple to
dosomething like this:<br /></font><blockquote><font face="Verdana">SELECT </font><font face="Verdana">SUM
(</font><fontface="Verdana">ROUND (o.quantity * i.price, 2))<br /> FROM orders o</font><font face="Verdana"><br /> JOIN
itemsi ON i.id_item = o.id_</font><font face="Verdana">item</font><br /><font face="Verdana">WHERE o.date_order BETWEEN
'2010-01-01'AND '2010-01-31';</font><br /></blockquote><br /> This multiplies the quantity of every line by the price
perunit, and sums the costs one by one. Done...<br /><br /> However, I'm obliged by the client to get an EXACT total
withNO DIFFERENCE of decimals (even though lots of them are "lost" during the rounded multiplications). He wants a
totalwhich MATCHES with the MANUAL sum of the results of the first query. It means that I need to do the same kind of
groupingwhich I perform on the first query and then sum all them. Hence, the reason behind my need.<br /><br />
PostgreSQLdoesn't allow nested SUMs, so I tried something like this:<br /><blockquote><font face="Verdana">SELECT SUM
(<br/></font><blockquote><font face="Verdana">(SELECT i.id_item, </font><font face="Verdana"> i.price, </font><font
face="Verdana">SUM(o.quantity), </font><font face="Verdana">ROUND (SUM (o.quantity) * i.price, 2) AS cost</font><br
/><fontface="Verdana">FROM orders o</font><br /><font face="Verdana">JOIN items i ON i.id_item = o.id_</font><font
face="Verdana">item</font><br/><font face="Verdana">WHERE o.date_order BETWEEN '2010-01-01' AND '2010-01-31'</font><br
/><fontface="Verdana">GROUP BY i.id_</font><font face="Verdana">item</font><font face="Verdana">, i.</font><font
face="Verdana">price</font><fontface="Verdana">)<br /></font></blockquote><font
face="Verdana">);</font></blockquote><br/> No luck. Obviously SUM expects an expression, not a set of rows. Is there a
wayto perform a sum of the resulting rows?<br /><br /> Thank you a lot.<font face="Verdana"><br /></font> 

Re: SUM the result of a subquery.

От
Jayadevan M
Дата:
> SELECT SUM (
> (SELECT i.id_item, i.price, SUM (o.quantity), ROUND (SUM 
> (o.quantity) * i.price, 2) AS cost
> FROM orders o
> JOIN items i ON i.id_item = o.id_item
> WHERE o.date_order BETWEEN '2010-01-01' AND '2010-01-31'
> GROUP BY i.id_item, i.price)
> ); 
> 
> No luck. Obviously SUM expects an expression, not a set of rows. Is 
> there a way to perform a sum of the resulting rows?
> 
I don't have a PostgreSQL server to try this right now. But you are 
looking for something like 
SELECT SUM (cost) from (
(SELECT i.id_item, i.price, SUM (o.quantity), ROUND (SUM (o.quantity) * 
i.price, 2) AS cost
FROM orders o
JOIN items i ON i.id_item = o.id_item
WHERE o.date_order BETWEEN '2010-01-01' AND '2010-01-31'
GROUP BY i.id_item, i.price)
) as x

Regards,
Jayadevan





DISCLAIMER: 

"The information in this e-mail and any attachment is intended only for 
the person to whom it is addressed and may contain confidential and/or 
privileged material. If you have received this e-mail in error, kindly 
contact the sender and destroy all copies of the original communication. 
IBS makes no warranty, express or implied, nor guarantees the accuracy, 
adequacy or completeness of the information contained in this email or any 
attachment and is not liable for any errors, defects, omissions, viruses 
or for resultant loss or damage, if any, direct or indirect."







Re: SUM the result of a subquery.

От
negora
Дата:
<font face="Verdana">Wow, I had no idea about this kind of SELECT expression. It works flawless!!! Thank you lots
Jayadevan</font><fontface="Verdana"> :) .</font><br /><font face="Verdana"><br /></font><br /> On 02/09/10 14:28,
JayadevanM wrote: <blockquote cite="mid:OF39AE7609.CC04F696-ON65257792.00446E7D-65257792.00448A26@ibsplc.com"
type="cite"><blockquotetype="cite"><pre wrap="">SELECT SUM (
 
(SELECT i.id_item, i.price, SUM (o.quantity), ROUND (SUM 
(o.quantity) * i.price, 2) AS cost
FROM orders o
JOIN items i ON i.id_item = o.id_item
WHERE o.date_order BETWEEN '2010-01-01' AND '2010-01-31'
GROUP BY i.id_item, i.price)
); 

No luck. Obviously SUM expects an expression, not a set of rows. Is 
there a way to perform a sum of the resulting rows?
   </pre></blockquote><pre wrap="">I don't have a PostgreSQL server to try this right now. But you are 
looking for something like 
SELECT SUM (cost) from (
(SELECT i.id_item, i.price, SUM (o.quantity), ROUND (SUM (o.quantity) * 
i.price, 2) AS cost
FROM orders o
JOIN items i ON i.id_item = o.id_item
WHERE o.date_order BETWEEN '2010-01-01' AND '2010-01-31'
GROUP BY i.id_item, i.price)
) as x

Regards,
Jayadevan





DISCLAIMER: 

"The information in this e-mail and any attachment is intended only for 
the person to whom it is addressed and may contain confidential and/or 
privileged material. If you have received this e-mail in error, kindly 
contact the sender and destroy all copies of the original communication. 
IBS makes no warranty, express or implied, nor guarantees the accuracy, 
adequacy or completeness of the information contained in this email or any 
attachment and is not liable for any errors, defects, omissions, viruses 
or for resultant loss or damage, if any, direct or indirect."





 </pre></blockquote>