CODEWITHSUNDEEP
sqlpostgresql
friend
friend

Reputation: 41

add new column to an existing table with addition

given below is my table that i want calculate according to the productid

  +-----------+-----------+----------+
 'productid   |     free  |  qty     |
 '------------+-----------+----------+
 '     2372   |      0    |    100   |
 '------------+-----------+----------+
 '     2311   |      0    |    100   |
 '------------+-----------+----------+
 '     2311   |      1    |     10   |
 '------------------------------------

and i need to get the following result

  +----------- +-----------+
 'productid   |     toqty  |  
 '------------+-----------+
 '     2372   |     100    |    
 '------------+-----------+
 '     2311   |     110    |     
 '-------------------------+

what i have tried is .

select sum(qty) as totQty 
from tmpprch 
where productid=(select productid from tmpprch)

and stuck here (ERROR: more than one row returned by a subquery used as an expression)

database : PostgreSQL

Upvotes: 1

Views: 63

Answers (2)

Ajay
Ajay

Reputation: 6590

You need to use Group By clause. Here is some important links http://www.w3schools.com/sql/sql_groupby.asp

http://technet.microsoft.com/en-us/library/ms177673.aspx

Here is simple Query

SELECT productid, SUM(qty) AS totQty
FROM tmpprch
Group By productid;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

If I understand this correctly, you are overcomplicating your query. This is a simple aggregation:

select productid, sum(qty) as totQty
from tmpprch
group by productid;

Where did you get the idea for your form of the query?

Upvotes: 3

Related Questions