CODEWITHSUNDEEP
sqlsql-server
user475353
user475353

Reputation:

Retrieve Count from SQL Join query

SELECT table.productid, product.weight
FROM table1
INNER JOIN product
ON table1.productid=product.productid
where table1.box = '55555';

Basically it's an inner join that looks at a list of products in a box, and then the weights of those products.

So i'll have 2 Columns in my results, the products, and then the weights of each product.

Is there an easy way to get the SUM of the weights that are listed in this query?

Thanks

Upvotes: 1

Views: 77

Answers (4)

SteveB
SteveB

Reputation: 799

I read it as you wanted a sum of the weights for that box as well as the rows for each product. I could be wrong but if not, here goes.

SELECT table1.productid, product.weight, SUM(weight) Over(PARTITION BY table1.box) AS SumWeights
FROM table1
INNER JOIN product
ON table1.productid=product.productid
WHERE table1.box = '55555';

Upvotes: 0

Ian
Ian

Reputation: 303

Not clear about the issue here, you just want the total weight or the grouping?

SELECT Sum(product.weight)
FROM table1
INNER JOIN product
ON table1.productid=product.productid
where table1.box = '55555';

will give the total.

Upvotes: 0

huMpty duMpty
huMpty duMpty

Reputation: 14460

SELECT table.productid, SUM(product.weight) weight
FROM table1
      INNER JOIN product
      ON table1.productid=product.productid
where table1.box = '55555'
Group By table.productid

Upvotes: 1

William
William

Reputation: 6610

This will give you the total weight for each distinct productid.

SELECT table1.productid, SUM(product.weight) AS [Weight]
FROM table1 INNER JOIN product ON table1.productid = product.productid
WHERE table1.box = '55555'
GROUP BY table1.productid

Upvotes: 0

Related Questions