CODEWITHSUNDEEP
sqlsqlite
Bigbob556677
Bigbob556677

Reputation: 2158

Sum Separate Columns In Two Different Tables

I have the table

Allocations:
|allocationid|     item     |     job     |    amount   |
|1           |     item1    |     jobname |      50     |
|2           |     item1    |     jobname |      30     |
|3           |     item2    |     jobname |      20     |

and the table

InventoryAdjustLog:
|   logid    |      item    |     customer |   usage    |
|   1        |     item1    |     jobname  |    70      |
|   2        |     item1    |     jobname  |     5      |
|   3        |     item2    |     jobname  |     15     |

Where the former table is an estimate of required materials for a job and the latter table is the actual usage.

I want to run a query on this data that will return the sum of all the like items that are in the Allocations table and the do another sum of all the like items in the InventoryAdjustLog table.

An example of this return using this data would be 

 |   Item    |     Job       |     Allocated     |     Usage       |
 |   item1   |     jobname   |     80            |    75           |
 |   item2   |     jobname   |     20            |    15           |

I have written the following query 

Select Allocations.item as Item ,Allocations.job as Job 
,sum(Allocations.ammount)as  Allocated,
sum(InventoryAdjustLog.usage) as Usage
from Allocations 
inner join InventoryAdjustLog on Allocations.job = InventoryAdjustLog.customer  
where Allocations.job= 'jobname' group by Allocations.item;

which only partially works with the exception that it sums every instance of the already summed row. As shown below. Since there are two rows joined, and both of which contain a summed value of 80 it sums the two rows into one.

 |   Item    |     Job       |     Allocated     |     Usage       |
 |   item1   |     jobname   |240 (Should be 80) |    etc...       |
 |   item2   |     jobname   | 60 (Should be 20) |    etc...          |

What modifications or changes in approach to my query would return the desired result as seen in the next to last table?

I'm by no means an expert in sql so any guidance in the matter is greatly appreciated.

Here is a SqlFiddle example

Upvotes: 1

Views: 55

Answers (2)

tysonwright
tysonwright

Reputation: 1525

SELECT
    item,
    job,
    SUM(amount) AS Allocated,
    SUM(usage) AS usage
FROM
    (
    SELECT
        item,
        job,
        amount,
        0 AS usage
    FROM 
        Allocations
    UNION ALL
    SELECT
        item,
        job,
        0 AS amount,
        usage
    FROM 
        InventoryAdjustLog  
    )
GROUP BY
    item,
    job

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You have a problem because you have duplicates in both tables. One solution is to combine the data using union all before doing the aggregation:

Select aia.item, aia.job,
       sum(aia.allocation)as  allocation,
       sum(aia.usage) as Usage 
from (select item, amount as allocation, 0 as usage, job
      from Allocations 
      union all
      select item, 0, usage, customer
      from InventoryAdjustLog 
     ) aia  
where aia.job = 'jobname'
group by aia.item, aia.job;

Here is a SQL Fiddle.

Upvotes: 2

Related Questions