CODEWITHSUNDEEP
mysql
Zamurph
Zamurph

Reputation: 89

How do I sum a column, and join it to another table based on a condition in SQL?

I have two tables in SQL, one that contains product_id, products_name, department_name, and product_sales and one that has department_id, department_name, and over_head_costs.

I want to be able to find the sum of all sales (grouped by department_name in table 1) and subtract the over_head_costs from table 2 so that I know how profitable a department is. Then I want to output the information like: department_id, department_name, over_head_costs, product/department sales, total_profit.

I've been searching for like 2-3 hours. I've messed around with joins (which I'm pretty sure is how to solve this) and found the SUM function, which achieves summing (but not by department) and honestly, even if I'd seen the solution I wouldn't know it. I'm just really struggling to understand SQL.

SELECT SUM(products.product_sales), department_id, departments.department_name, over_head_costs 
FROM products, departments 
WHERE products.department_name = departments.department_name;

This is my most recent query and the closest I've gotten, except it only returns one department (I currently have 3).

This is roughly what I’d like it to look like:

Table 1 (products):

ID    ITEM         DEPARTMENT                SALES

1      Hammer       Tools                     40

2.     Nails        Tools                     40

3.     Keyboard     Computer                  80

Table 2 (departments):

ID    DEPARTMENT    COST

1      Tools        20 

2.     Computer     30

Output:

ID    DEPARTMENT    COST     SALES      PROFIT

1       Tools       20         80               60

2.      Computer    30         80               50

I'm not really sure what else to try. I think I'm just not understanding how joins and such work. Any help would be greatly appreciated.

Upvotes: 2

Views: 44

Answers (1)

D-Shih
D-Shih

Reputation: 46239

You can try to use SUM wiht group by in a subquery. then do join.

Query 1:

SELECT d.*,
       t1.SALES,
      (t1.SALES - d.COST)PROFIT
FROM (
    SELECT DEPARTMENT,SUM(SALES) SALES 
    FROM products
    GROUP BY DEPARTMENT
) t1 JOIN  departments d on d.DEPARTMENT = t1.DEPARTMENT

Results:

| DEPARTMENT | COST | SALES | PROFIT |
|------------|------|-------|--------|
|      Tools |   20 |    80 |     60 |
|   Computer |   30 |    80 |     50 |

Upvotes: 1

Related Questions