CODEWITHSUNDEEP
sqlgoogle-bigquery
christianhchristianh
christianhchristianh

Reputation: 45

Join tables and accumulate values

I'm a SQL rookie.

Table A contains information about my projects. Here we have a field for "ExpectedValue_amount" - meaning. How much were we expecting to earn/invoice on this project.

Table B contains our actual invoices. One project can have multiple invoices. So I want to accumulate all values on a certain project to see the difference between "ExpectedValue" and "Actual invoice value".

My query for Table A:

SELECT
  name,
  number,
  customer_name,
  expectedValue_amount,
FROM
  Projects

In Table B I have projectnumber which == number in Table A. But I want to see the total on a project.

SELECT
  projects_number,
  totalExcludingTax_amount
FROM
    Invoices

Desired output: Desired output

Upvotes: 0

Views: 123

Answers (2)

S.Jackson
S.Jackson

Reputation: 159

SELECT
  p.name,
  p.number,
  p.customer_name,
  p.expectedValue_amount,
  SUM(i.totalExcludingTax_amount) as totalExcludingTax_amount
FROM
  Projects p 
JOIN 
  Invoices i 
  ON CAST(p.number as STRING) = i.projects_number
GROUP BY 1,2,3,4

Upvotes: 0

Claus Nielsen
Claus Nielsen

Reputation: 551

Maybe try to mess a little around with joining the tables but something like this may work for you

SELECT
  p.name,
  p.number,
  p.customer_name,
  p.expectedValue_amount,
  SUM(i.totalExcludingTax_amount)
FROM
  Projects p JOIN Invoices i ON i.projects_number = p.number
GROUP BY p.number

Upvotes: 1

Related Questions