SQL query design help needed

Question

I am trying to select only one of the records of multiple agregated tables with this query:

select * from table1 C left join
              table2 E on C.id=E.id left join 
              table3 D on E.id=D.id 
              where C.id=id and (E.id IS NULL or D.id IS NOT NULL)
              GROUP BY C.field

When execute this query a SQLException is launched:

the expression is not in an agreement or columns of the GROUP BY

What wrong in the query?

Thanks

http://stackoverflow.com/questions/33629168/group-by-clause-in-mysql-and-postgresql-why-the-error-in-postgresql — Lukasz Szozda, Dec 11 '15 at 11:25
The general GROUP BY rule is: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function. — jarlh, Dec 11 '15 at 11:29
Aggregation means you take several records and produce one result record from them, by taking columns' sums, average values, maximums, or whatever. You get either only one total result or you get a row per specified group (which is when `GROUP BY` comes into play). You are not aggregating anything here though, no `SUM`, no `MAX`, nothing. So what are you trying to do? — Thorsten Kettner, Dec 11 '15 at 11:38
Please tag your DBMS, `mysql` for instance is a lot more (too much, in my opinion) forgiving when it comes to `groupb by` — HoneyBadger, Dec 11 '15 at 11:41
As the others mentioned.I just add a reference (http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/but-why-must-that-column-be-contained-in-an-aggregate.aspx) for better understanding — punitcse, Dec 11 '15 at 11:41
Without the GROUP BY clause the query returns several records representing Cars. Some records are the same car (id), I'm trying to group all the same cars in one record. — Hanzo, Dec 11 '15 at 11:54

score 1 · Answer 1 · edited May 23 '17 at 12:30

I am trying to select only one of the records of multiple agregated tables with this query:
select * from...

Actually you are not. Per the SQL Standard the asterisk denotes all columns and is not compatible with a GROUP BY statement.

In this query:

select * from 
...
GROUP BY C.field

What you are actually saying is "Give me everything from these tables but only GROUP BY on one column.

The SQL engine is not smart like a human and cannot discern what you want to do with those other columns so it sees another 5 or 10 columns that you are asking to pull back and it cannot reconcile them with your GROUP BY request on one column.

For a solution, try (1) replacing SELECT * with SELECT c.Field or (2) explicitly listing all columns in your SELECT statement and then also list them in the GROUP BY statement.

score 0 · Answer 2 · answered Dec 11 '15 at 12:36

If you want one row per "group", then use row_number() or a similar function:

select t.*
from (select *, row_number() over (partition by c.field order by c.field) as seqnum
      from table1 C left join
           table2 E
           on C.id = E.id left join 
           table3 D
           on E.id = D.id 
     where C.id = id and (E.id IS NULL or D.id IS NOT NULL)
    ) t
where seqnum = 1;

Your original query does not work because you have columns in the SELECT that are not in the GROUP BY. This is not allowed by the ANSI standard and not allowed by most databases.

* will never work with GROUP BY. The reason is that the columns that you return should either returned as a calculated value (like sum or average) or they should be mentioned in the GROUP BY statement. Working with a * to get all the fields back is also considered bad practice. — Tom, Dec 11 '15 at 12:58

SQL query design help needed

2 Answers2