sql - HIVE Group By in Multiple Tables


Keywords:sql 


Question: 

Two tables - 'salaries' and 'master1'

The salaries is by year, and I can group them to get the sums for each player using

SELECT playerID, sum(salary) as sal 
FROM salaries 
GROUP BY playerID ORDER BY sal DESC LIMIT 10; 

This returns the playerID and sum of salary, but I need the names of the players from the 'master1' table under column 'nameFirst' and 'nameLast'. They have the common column of 'playerID' in both 'master1' and 'salaries' but when I try to run

SELECT master1.nameFirst, master1.nameLast, sum(salary) as sal 
FROM salaries, master1 
GROUP BY salaries.playerID ORDER BY sal DESC LIMIT 10; 

I get the error Expression not in GROUP BY key 'nameFirst'

I have tried tinkering with it to continue getting errors.

Thanks!


2 Answers: 

Need to include nameFirst and nameLast in the group by:

SELECT 
master1.nameFirst, 
master1.nameLast, 
sum(salary) as sal 
FROM salaries JOIN master1 ON salaries.playID = master1.playerID
GROUP BY master1.nameFirst, master1.nameLast, salaries.playerID 
ORDER BY sal DESC LIMIT 10;
 

First, you would need to use proper explicit JOIN syntax

SELECT  
        MAX(m.nameFirst) FirstName,
        MAX(m.nameLast) LastName,
        SUM(s.salary) Salary
FROM master1 m
INNER JOIN salaries s ON m.playerID = s.playerID 
GROUP BY m.playerID 

Use, master1 table to get the FirstName, LastName and do the JOIN with salaries table to get the total salary of each player.

For, your current query exception when you are using GROUP BY clause make ensure that the columns/expressions in SELECT statement needs to be aggregate.