CODEWITHSUNDEEP
sqlpostgresqlnullaggregate
Saurabh Omar
Saurabh Omar

Reputation: 39

Return 0 for all rows where a sum was not done due to join condition not satisfied

We are trying to do an aggregate of a column on Tables A based on Table B. We see that while joining both the tables, we are only getting values where the join condition was satisfied. However, for the other rows, the value was null.

Table A 

SKU | Quantity | Amount  
A   |  
B   |  
C   |  
D   |  
E   |  
F   |

Table B 

Order | SKU | Quantity | Amount  
1     | A   | 5        | 50  
2     | B   | 50       | 2000  
3     | C   | 100      | 5000  
4     | D   | 50       | 60  
5     | A   | 20       | 200  
6     | A   | 60       | 600  
7     | B   | 10       | 400

We want to calculate the sum of Qty and amount in table A from table B. We are using the following script

UPDATE Table A x 
   SET Quantity = y.qty 
from (
   SELECT SKU, SUM(quantity) AS qty 
   FROM Table B 
   GROUP BY SKU
) AS y 
WHERE x.SKU=y.SKU;

On doing this, we are getting

Table A 

SKU | Quantity | Amount  
A   | 85       |   
B   | 60       | 
C   | 100      |  
D   | 50       |  
E   |          |  
F   |          |

We would like to have E and F updated as 0. We tried using Coalesce(SUM(quantity),'0') but then realized that this will not update the values to 0 as there is no join between Table A and B for SKUs E & F.

Is this possible?

Upvotes: 0

Views: 69

Answers (1)

user330315
user330315

Reputation:

There are two ways to do this. One is to use a co-related sub-query:

UPDATE A 
   SET Quantity = (select coalesce(sum(quantity),0) 
                   from b where b.sku = a.sku);

The other - probably faster way - is to use an outer join in the derived table from your original query:

update a 
   set quantity = y.qty 
from (
   select a.sku, coalesce(sum(b.quantity),0) as qty 
   from a 
     left join b on a.sku = b.sku
   group by a.sku
) as y 
where a.sku = y.sku;

Upvotes: 0

Related Questions