SQL Group by error - "not a GROUP BY expression"

Question

I seem to be having a problem with my SQL query that's driving me mad. I just can't seem to get the group by to work. I keep getting the following error:

00979. 00000 -  "not a GROUP BY expression"

My query:

SELECT customers.customer_first_name, customers.customer_last_name, orders.customer_numb, books.author_name, books.title
FROM customers
LEFT OUTER JOIN orders ON (orders.customer_numb = customers.customer_numb)
LEFT OUTER JOIN order_lines ON (order_lines.order_numb = orders.order_numb)
LEFT OUTER JOIN books ON (books.isbn = order_lines.isbn)
WHERE (customers.customer_numb = 6)
GROUP BY (books.title)

Database schema:

Customers:

enter image description here

Order Lines & Orders:

enter image description here

What I'm trying to achieve: I'm trying to group by book titles so that it doesn't show duplicate titles.

I apologise if I've missed anything, thanks.

There is no aggregate function in your query. Why a `group by` then? — Anjan Biswas, Oct 12 '12 at 18:25
Check that all the columns in the select statement are in the group by... — Humberto Castañeda, Oct 12 '12 at 17:14

score 14 · Accepted Answer · answered Oct 12 '12 at 17:11

14

What don't you understand? Your select statement contains many columns that are not in the group by clause. Try this:

SELECT customers.customer_first_name, customers.customer_last_name, orders.customer_numb, books.author_name, books.title 
FROM customers 
LEFT OUTER JOIN orders ON (orders.customer_numb = customers.customer_numb) 
LEFT OUTER JOIN order_lines ON (order_lines.order_numb = orders.order_numb) 
LEFT OUTER JOIN books ON (books.isbn = order_lines.isbn) 
WHERE (customers.customer_numb = 6) 
GROUP BY customers.customer_first_name, customers.customer_last_name, orders.customer_numb, books.author_name, books.title

Since you are not aggregating anything, you can dispense with the group by, and just use distinct in the select clause:

select distinct  customers.customer_first_name, customers.customer_last_name, orders.customer_numb, books.author_name, books.title

answered Oct 12 '12 at 17:11

Gordon Linoff

1,242,037
58
646
786

Your answer works perfectly. One question, will the group by only work when I select the same values as what's in SELECT? – Oct 12 '12 at 17:14
In standard SQL and most database engines, all columns that are not part of aggregation functions (like SUM, AVG, MIN, MAX), need to be in the `group by` clause. – Gordon Linoff Oct 12 '12 at 17:18
But there is no Agg function on any of the columns selected in the query. Why a `GROUP BY` then? I don't get it. – Anjan Biswas Oct 12 '12 at 18:23
@Annjawn . . . A `group by` in this case has the same effect as `distinct`. – Gordon Linoff Oct 13 '12 at 01:36

score 0 · Answer 2 · answered Oct 12 '12 at 17:11

0

SELECT DISTINCT customers.customer_first_name, customers.customer_last_name, orders.customer_numb, books.author_name, books.title
FROM customers
LEFT OUTER JOIN orders ON (orders.customer_numb = customers.customer_numb)
LEFT OUTER JOIN order_lines ON (order_lines.order_numb = orders.order_numb)
LEFT OUTER JOIN books ON (books.isbn = order_lines.isbn)
WHERE (customers.customer_numb = 6)

Use DISTINCT instead Maybe?

answered Oct 12 '12 at 17:11

cagcowboy

30,012
11
69
93

Yes, I think he wants to use a `disinct`. `group by` is used if there is an aggregate function used in any of the column selected. – Anjan Biswas Oct 12 '12 at 18:25

score 0 · Answer 3 · answered Oct 12 '12 at 18:22

It sounds like what you want is a list of customers and the distinct books that they've bought. I'd rewrite the query like this, which ought to do what you want:

select c.customer_first_name ,
       c.customer_last_name  ,
       c.customer_numb       ,
       b.author_name         ,
       b.title               
from customers c
left join ( select distinct
                   o.customer_numb ,
                   ol.isbn         
            from      orders      o
            left join order_lines ol on ol.order_number = orders.order_numb
          ) cb on cb.customer_numb = c.customer_numb
join books b on b.isbn = cb.isbn
where c.customer_numb = 6

If you want a count of how many of each title they've bought, change the derived table (aka inline view or virtual table) in the from clause to use group by rather than select distinct and add the appropriate aggregate function to the result set.

Here's where your original query goes south: every column in the result set must be either

a column or expression in the group by clause,
a literal, or ...
an aggregate function

While there are some exceptions here (for instance, you can have an expression dependent solely on grouping columns and aggregate functions), and whilst the SQL Standard putatively allows other columns, most implementations do not.

Consider a query like this, where you have a one-to-many relationship between customer and order, where individual orders might be shipped to a one address or another.

select c.customer_id , o.shipping_address , orders = count(*)
from customer c
join order    o on o.customer_id
group by c.customer_id

What is the semantic meaning of o.shipping_address in this context? You've grouped the orders by customer id and collapsed the entire group into a single row. customer_id is easy, since that's the grouping criteria: the entire group, by definition shares the same value for it. count(*) is easy, too, since it's merely a count of the rows in the group.

'shipping_address' is a problem, though: the group might well have multiple values for shipping address, but the aggregated group can only return one value. First? Last? something else?

SQL Server used to have a very nonstandard implementation that allowed such oddball stuff. What it did in this case is to essentially aggregate the group into a single row and then join that aggregated across each row across all the rows in the group. Not exactly intuitive behaviour.

SQL Group by error - "not a GROUP BY expression"

3 Answers3

Linked