CODEWITHSUNDEEP
sqlgroup-by
Arthur Pinhas
Arthur Pinhas

Reputation: 77

How to group by 1 column while selecting a multiple columns

I have tried to get employee information from one table while doing a group by on columns from another table and i seem to be getting duplicates. I need to group by the year while showing the employee which made the highest priced sale for the year.

what i tried:

select concat(SM.FirstName,'-',SM.LastName) AS EmployeeOfTheYear,Year(S.SaleDate) AS SaleYear,Max(S.SalePrice) AS SalesPrice
   from SalesMan SM,Sale S
   where SM.SalesManID = S.SalesManID
   group by Year(S.SaleDate),concat(SM.FirstName,'-',SM.LastName)

The result:

Result

I have a feeling i should be doing a subquery here. i have tried looking on the web and couldn't find anything to help me display the year only once.

Upvotes: 0

Views: 93

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

First, I would use proper, explicit JOIN syntax. Second, the most efficient mechanism is often a correlated subquery. This is not an aggregation at all:

select concat(SM.FirstName,'-',SM.LastName) AS EmployeeOfTheYear,
       Year(S.SaleDate) AS SaleYear,
       S.SalePrice AS SalesPrice
from SalesMan SM join
     Sale S
     on SM.SalesManID = S.SalesManID
where s.saleprice = (select max(s2.saleprice)
                     from sale s2
                     where s2.SalesManID = s.SalesManID
                    );

With the right indexes, the correlated subquery often has better performance than alternatives.

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

You could use row_number() partitioning by year and ordering by the price descending.

SELECT concat(sm.firstname, '-', sm.lastname) employeeoftheyear,
       x.saleyear,
       x.saleprice
       FROM (SELECT year(s.saledate) saleyear,
                    s.salesmanid,
                    max(s.saleprice) saleprice,
                    row_number() OVER (PARTITION BY year(s.saledate)
                                       ORDER BY max(s.saleprice) DESC) rn
                    FROM sale s
                    GROUP BY year(s.saledate),
                             s.salesmanid) x
            LEFT JOIN salesman sm
                      ON sm.salesmanid = x.salemanid
       WHERE x.rn = 1
       ORDER BY x.saleyear DESC;

(I don't know which DBMS you're on. Most of the support row_number() nowadays. I hope yours does too. It's probably SQL Server, where it should work.)

Upvotes: 1

stephen barter
stephen barter

Reputation: 421

Sounds like you need a JOIN clause. This will return only the intersection of the two sets. https://www.w3schools.com/sql/sql_join_inner.asp

Upvotes: 1

Related Questions