Solved

# Query with SUM

Posted on 2011-10-05
356 Views
Hello, here is my SQL problem:

table A:

id_row | products | price |
-------------+----------+-------------
1 |        PDA |      500.00 |
2 |        PDX |      650.00 |
3 |       PDB  |           3.00 |
4 |        PDC |      100.00 |
5 |       PDC  |           5.00 |
6 |       PDD  |       100.00 |

table B:

id_row_table A | price |
---------------------+-------------
1 |           500.00 |
1 |           650.00 |
2 |             40.00 |

I would like to have this result view:

id_row | product | price | pricesumfromtableB
---------------------+------------+--------------------------
1 |   PDA |  500.00 |    1150.00
2 |   PDX |  650.00 |         40.00

thanks to all.

0
Question by:wakatanka

LVL 73

Expert Comment

sselect a.id_row,a.product,a.price,sum(b.price)
from a,b
where a.id_row = b.id_row_table_a
group by a.id_row,a.product,a.price
0

LVL 73

Expert Comment

also...

sselect a.id_row,a.product,a.price,sum(b.price)
from a inner join b
on  a.id_row = b.id_row_table_a
group by a.id_row,a.product,a.price
0

Author Comment

HI sdstuber, thanks for your solution, but in this way it returns only row that have some record in table b, i
need also all the other rows in table A.
0

LVL 73

Expert Comment

your example doesn't show that,  it only shows rows that are in both tables...

but, it's easy enough, try this...

select a.id_row,a.product,a.price,(select sum(b.price) from b where  a.id_row = b.id_row_table_a)
from a
0

LVL 73

Accepted Solution

alternately...  change the inner above to outer

sselect a.id_row,a.product,a.price,sum(b.price)
from a left outer join b
on  a.id_row = b.id_row_table_a
group by a.id_row,a.product,a.price
0

Author Closing Comment

Speedy answer and very clear. great.
0

## Featured Post

### Suggested Solutions

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
This video discusses moving either the default database or any database to a new volume.