CODEWITHSUNDEEP
sqlgroup-by
Brad
Brad

Reputation: 157

Using the Group By Function in SQL

I have to find the number of products sold by Boris Davidovich (SalesmanId "6"). But I have to use the name in the query, and not the ID, and I would like to use GROUP BY.

So far this is what I have written:

SELECT Salesmen.FirstName, Salesmen.LastName,  COUNT(ProductsSales.QuantitySold)
FROM ProductsSales LEFT JOIN Salesmen
ON Salesmen.FirstName = Boris AND Salesmen.LastName = Davidovich

I'm stuck from where I go from here.

Here is the data that I have to work with.

enter image description here

Upvotes: 0

Views: 37

Answers (2)

Alexander Bell
Alexander Bell

Reputation: 7918

Your SQL Select statement could be modified as shown below:

    SELECT First(Salesmen.FirstName) As FN, Fist(Salesmen.LastName) As LN, SUM (ProductsSales.QuantitySold) As TotalProd 
    FROM Salesmen  INNER JOIN ProductsSales  ON Salesmen.SalesmanId = ProductsSales.SellerId 
    WHERE (Salesmen.FirstName= 'Boris' AND Salesmen.LastName = 'Davidovich')
GROUP BY Salesmen.SalesmanID

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

Assuming the sellerid is the salesmanid, then you need to change your join to match on those fields. The you can use group by accordingly, but with sum instead of count:

SELECT s.SellerId, s.FirstName, s.LastName, SUM(ps.QuantitySold) 
FROM Salesmen S JOIN 
    ProductsSales PS ON S.sellerid = PS.salesmanid 
WHERE S.FirstName = 'Boris' AND S.LastName = 'Davidovich'
GROUP BY s.SellerId, s.FirstName, s.LastName

Also, there's no need for an outer join unless you want to return sales people without any sales. If that's the case, you'll want to change the inner join to a left join and use coalesce to get 0 instead of null.

Upvotes: 1

Related Questions