当前位置: 动力学知识库 > 问答 > 编程问答 >

How to sum the numbers of a column in a secondary table with SQL

问题描述:

I have a Table Owners

Owner product

Jhon product1

Jhon product2

Jhon product3

Chris product4

Another Table: Products

Product QuantitySold

Product1 3

Product2 5

Product3 2

Product4 7

How do I do a SQL to come up with the following result:

  1. Name of Owner

  2. Number of products

  3. QuantitySold

    NameOfOwner NumberOfProducts QuantitySold

    Jhon 3 10 (3+5+2)

    Chris 1 7

I tried:

select Owners.owner, count(distinct Owners.product) as NumberOfProducts, sum(Product.QuantitySold) as QuantitySold from Owners O, Products P

group by O.owner

But that returns the total of quantitySold for any owner (17 = 3+5+2+7) multiple by the number of products.

NameOfOwner NumberOfProducts QuantitySold

Jhon 3 51 = 3 * 17 (3+5+2+7)

Chris 1 17 = 1 * 17 (3+5+2+7)

Thank you very much

网友答案:
select o.Name, count(o.Name) NumberOfProducts, sum(QuantitySold) QuantitySold from #Owner o inner join #Product p on p.Name=o.Product
Group By o.Name
网友答案:

You have to use a inner join (or other kind of join):

select owner, count(a.product) nr_of_products, sum(quantity) Qnt_Sold from Owners inner join Products on Owners.product = Products.product group by owner

Different SQL JOINs:

INNER JOIN: Returns all rows when there is at least one match in BOTH tables

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

FULL JOIN: Return all rows when there is a match in ONE of the tables

网友答案:
select a.owner as NameOfOwner, count(a.Product) as NumberOfProducts,sum(QuantitySold) as QuantitySold from Owners as a 
INNER JOIN Products on a.Product = Products.Product group by a.owner
网友答案:

Pretty much a repeat here of the other answers, but including a run on Sql Fiddle.

SELECT o.owner as Owner, count(o.owner) as NumberofProducts, sum(p.quantitySold) as Quantity
FROM Owners o
inner Join Products p
on o.product = p.product
Group by o.owner
分享给朋友:
您可能感兴趣的文章:
随机阅读: