Query Sum of the column based on query from another table

Question

I need to make a query that sums a column based on a value from another table.

So I have two tables

PROJECT

PROJECT_NO PROJECT_NAME
W14026     SMSMILLHOUSE
W14026     SMSSUGARWHOUSE
W14026     SMSBOILERHOUSE
W-IGG      IGGMILLHOUSE
W-IGG      IGGBOILERHOUSE

DTL_ERC_UPD

PROJECT_NAME   QUANTITY
SMSMILLHOUSE   5
SMSMILLHOUSE   2
SMSBOILERHOUSE 3
IGGMILLHOUSE   4
IGGMILLHOUSE   5

So i want to sum all the W14026 project that is in DTL_ERC_UPD. that should give the output of 10. I dont know how to approach that. I know simple join just doesnt work. Please help me,

What do you mean by simple join didn't work ?

Mahesh
– Mahesh

2015-02-10 07:41:49 +00:00
Commented Feb 10, 2015 at 7:41 — Mahesh
– Mahesh, Commented Feb 10, 2015 at 7:41

starko · Accepted Answer · 2015-02-10 08:06:36Z

3

select project_no, sum(d.quantity) 
from project p join dtl_erc_upd d
on p.project_name=d.project_name
group by project_no

edited Feb 10, 2015 at 8:06

answered Feb 10, 2015 at 7:37

starko

1,14911 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ke.Cyrus · Accepted Answer · 2015-02-10 08:48:52Z

0

This should solve all your problems:

SELECT SUM(dtl_erc_upd.quantity) AS project_count 
FROM dtl_erc_upd 
INNER JOIN project 
ON project.project_name = dtl_erc_upd.project_name

edited Feb 10, 2015 at 8:48

answered Feb 10, 2015 at 7:51

Ke.Cyrus

262 bronze badges

Collectives™ on Stack Overflow

Query Sum of the column based on query from another table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related