I have a Table Owners

Owner product

``Jhon product1Jhon product2Jhon product3Chris product4``

Another Table: Products

Product QuantitySold

``Product1 3Product2 5Product3 2Product4 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 Pgroup 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 QuantitySoldJhon 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
``````

Top