[SQL] SUM the result of a subquery.
Hello: 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: 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; This just groups items of several orders by item ID, sums the quantities, multiplies such amounts by the price per unit, and rounds the result to 2 decimals. Very easy. The cost calculation is performed using the sum of the quantities instead of doing it per line to "lose" as less decimals as possible, because a rounding is applied on every multiplication. Now I need to get the total of ALL that lines in a separate query. It'd be really simple to do something like this: SELECT SUM (ROUND (o.quantity * i.price, 2)) 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'; This multiplies the quantity of every line by the price per unit, and sums the costs one by one. Done... However, I'm obliged by the client to get an EXACT total with NO DIFFERENCE of decimals (even though lots of them are "lost" during the rounded multiplications). He wants a total which MATCHES with the MANUAL sum of the results of the first query. It means that I need to do the same kind of grouping which I perform on the first query and then sum all them. Hence, the reason behind my need. PostgreSQL doesn't allow nested SUMs, so I tried something like this: 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? Thank you a lot.
[SQL] SUM the result of a subquery.
Hello: 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: 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; This just groups items of several orders by item ID, sums the quantities, multiplies such amounts by the price per unit, and rounds the result to 2 decimals. Very easy. The cost calculation is performed using the sum of the quantities instead of doing it per line to "lose" as less decimals as possible, because a rounding is applied on every multiplication. Now I need to get the total of ALL that lines in a separate query. It'd be really simple to do something like this: SELECT SUM (ROUND (o.quantity * i.price, 2)) 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'; This multiplies the quantity of every line by the price per unit, and sums the costs one by one. Done... However, I'm obliged by the client to get an EXACT total with NO DIFFERENCE of decimals (even though lots of them are "lost" during the rounded multiplications). He wants a total which MATCHES with the MANUAL sum of the results of the first query. It means that I need to do the same kind of grouping which I perform on the first query and then sum all them. Hence, the reason behind my need. PostgreSQL doesn't allow nested SUMs, so I tried something like this: 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? Thank you a lot.
Re: [SQL] SUM the result of a subquery.
Wow, I had no idea about this kind of SELECT _expression_. It works flawless!!! Thank you lots Jayadevan :) . On 02/09/10 14:28, Jayadevan M wrote: 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: [SQL] all the table values equal
I'm not sure if I understood you right, but it sounds similar to a case which I faced recently. Why not to use an inverse approach? In other words: trying to find those registries which hasn't got at least one value which differs from which you want to look for. How? Using the EXISTS function with some kind of subquery I guess. On 21/09/10 10:11, Michele Petrazzo - Unipex wrote: Oliveiros d'Azevedo Cristina ha scritto: Hello again, Michele, Ciao, I haven't open my mailbox during weekend so I couldn't follow up your question. No problem! It would help if you explain a little better the background of the problem you're trying to solve. You want to find all the user IDs which have the same value on a given time interval? Is my understanding correct? Yes. Re-reading my post I saw that I could explain better! id_user | value | datetime 1 | 1 | xxx 1 | 2 | xxx+1 1 | -1 | xxx+2 2 | -1 | xxx 2 | -1 | xxx+1 3 | 4 | xxx 3 | 10 | xxx+1 3 | 4 | xxx+2 4 | 3 | xxx 4 | 3 | xxx+1 So, the new question: how I can find which id_user has _all_ the "value" that I'm looking for? Say -1 as 3 and I want a id_user=2 for the first and for the latter id_user=4 Thanks, Michele Best, Oliver - Original Message - From: "Michele Petrazzo - Unipex" To: "Oliveiros d'Azevedo Cristina" Cc: Sent: Friday, September 17, 2010 4:45 PM Subject: Re: [SQL] all the table values equal Oliveiros d'Azevedo Cristina ha scritto: Howdy , Michele, Give this a try SELECT id_user FROM t_your_table WHERE datetime BETWEEN A -- plug here beginning of interval AND B -- and end here GROUP BY id_user HAVING COUNT(*) = -SUM(value) Then tell me if it gives you what you want Thanks, it works, but... it's really a trickle that exploits the value -1 if I understand how its work. If there is another value where look for? Example 13? Thanks -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] HowTo divide streetname from house-nr ?
I guess that it's impossible to look for a solution which works on every existing case, specially if you're handling addresses from several countries. However, if you've certain control over the user inputs, maybe you could try replacing certain parts employing some kind of regular _expression_ which removes numbers at the beginning/end of the string. On 23/09/10 03:25, Andreas wrote: Hi, how could I divide streetnames from housenumbers ? I have to deal with input like this: Parkstreet 42 Parkstr. 42 Casle Avenue 42 Casle Str. 42-47 Casle Str. 54 - 55 probaply even Casle Str. 42-47 a Perhaps one could cut ap the 1st numeric char and regard everything left of it as the street name and the rest as house number. OK, this would fail with "42, Parkstreet" but those aren't to frequent. How would I do this?
