Select a column that is not in the GROUP BY clause

Question

I need advice on how to do SQL queries that I returned the following: I have 2 tables: customer and the department

SELECT a.id, a.first_name, a.last_name, MIN (b.income), b.department
/* --b.department can not be in a GROUP BY clause, 
   --but I need to know which department has the
   --smallest income, i.e. which department is responsible for MIN (b.income) */
FROM CUSTOMERS a
INNER JOIN department b
ON a.id = b.id
GROUP BY a.id, a.first_name, a.last_name;

How can I do it?

Join the subquery with MIN with a select which contains the department ON subquery.id-table.id and sub.income=table.income.Using id also in case of duplicate incomes. — Mihai, Jul 30 '14 at 20:54
You can use a window function for this kind of requirements. — , Jul 30 '14 at 20:59
Could you clarify what does column 'id' pertain? Is it id of the department or id of the customer? And what kind of relationship does the join operation mean in the real world? — Tomasz Siorek, Jul 30 '14 at 21:53

score 1 · Answer 1 · answered Jul 30 '14 at 22:37

You can use the PostgreSQL-specific feature distinct on to do this:

SELECT distinct on (a.id, a.first_name, a.last_name) 
     a.id, a.first_name, a.last_name, b.income, b.department
FROM CUSTOMERS a
INNER JOIN department b
ON a.id = b.id
ORDER BY a.id, a.first_name, a.last_name, b.income;

This means you get one row for each set of distinct values in the distinct on (...), and which row in that set you get is the first one (as determined by the order by) in that group

Matthew Haugen · Answer 2 · 2014-07-30T21:24:50.040

This looks like T-SQL so I'll give the answer for that.

SELECT a.id,
       a.first_name,
       a.last_name,
       MIN(b.income),
       (SELECT TOP 1 c.departmentname --Or whatever the name of your department name is
        FROM department c
        WHERE c.income = MIN(b.income)) AS [DepartmentName]
FROM CUSTOMERS a
INNER JOIN department b ON a.id = b.id
GROUP BY a.id, a.first_name, a.last_name;

You need to use a nested query in order to find which department has the income. You might also have to add in some more where restraints on the nested query there, assuming multiple departments can have the same income. But those will depend on your database schema, so I'll leave you to work out that logic to make sure you're talking about the same one.

Edit:

Although reading this more, it looks like you could just rephrase it all:

SELECT a.id,
       a.first_name,
       a.last_name,
       (SELECT TOP 1 departmentname --Or whatever the name of your department name is
        FROM department
        WHERE department.id = customers.id
        ORDER BY income DESC) AS [DepartmentName]
FROM customers

You wouldn't get the income with that, but you can add in the code to get that too.

Yes, it is possible that several departments have the same income. The top command me then returns 1 department with a minimum income? — peterko, Jul 30 '14 at 21:23

Pred · Answer 3 · 2014-07-30T21:14:11.090

0

In T-SQL (and PL/SQL and in most RDBMS) you can use the OVER clause (windowing):

SELECT a.id, a.first_name, a.last_name,
-- Here is the trick
MIN (b.income) OVER (PARTITION BY a.id, a.first_name, a.last_name) AS min_income,
-- End of trick
b.department
FROM CUSTOMERS a
INNER JOIN department b
ON a.id = b.id

edited Jul 30 '14 at 21:14

answered Jul 30 '14 at 21:07

Pred

8,789
3
26
46

score -1 · Answer 4 · answered Jul 30 '14 at 20:58

Something like

Select cust.*, b.department from
(SELECT a.id, a.first_name, a.last_name, MIN (b.income) min_income
FROM CUSTOMERS a
GROUP BY a.id, a.first_name, a.last_name
) cust
INNER JOIN department b
ON cust.id = b.id

If your db supports this syntax.

Select a column that is not in the GROUP BY clause

4 Answers4