SQL group by "column name"

Question

I cant seem to see why this query is failing, in my group by clause I have the columns that are in my select yet im still getting the error. Why is this? Below is my query;

SELECT c.customer_first_name, c.customer_last_name, MAX(SUM(cost_line))
FROM customers c LEFT OUTER JOIN orders o USING(customer_numb)
LEFT OUTER JOIN order_lines l USING (order_numb)
GROUP BY c.customer_first_name, c.customer_last_name
ORDER BY customer_numb;

getting this error

SQL Error: ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"

You don't need `l.cost_line` in your `SELECT` as that's the aggregate being used. — Kermit, Nov 04 '13 at 20:58
You can't nest aggregate functions the way you have it - you can't use `max(sum())` — Taryn, Nov 04 '13 at 21:00
I have added the error above, i have also removed that cost_line but it still gave me the same error — Luke14, Nov 04 '13 at 21:01
possible duplicate of [SQL not a single-group group function](http://stackoverflow.com/questions/1795198/sql-not-a-single-group-group-function) — Yosi Dahari, Nov 04 '13 at 21:04

score 2 · Answer 1 · answered Nov 04 '13 at 21:03

2

You need to get rid of the l.cost_line in the GROUP BY, as mti2935 suggests, and also get rid of the max() function -- you can't use multiple aggregate functions like this.

SELECT c.customer_first_name, c.customer_last_name, SUM(cost_line)
FROM customers c LEFT OUTER JOIN orders o USING(customer_numb)
LEFT OUTER JOIN order_lines l USING (order_numb)
GROUP BY c.customer_first_name, c.customer_last_name
ORDER BY customer_numb;

http://sqlfiddle.com/#!2/fdbba1/6

answered Nov 04 '13 at 21:03

jokeeffe

395
1
6

1

I'd also suggest getting rid of the "ORDER BY customer_numb" clause. MySQL (which this question was originally tagged as) is permissive in letting you SELECT and ORDER BY columns that aren't part of your group criteria, but most other RDBMS will flag this as an error as well. Either sort by first/last name, or add the customer number to your group criteria. – jokeeffe Nov 04 '13 at 21:05

score 0 · Answer 2 · answered Nov 04 '13 at 20:58

0

I think the problem is that you should not have l.cost_line in the GROUP BY clause, because you are using this field in an aggregate function in the SELECT clause. Try it without l.cost_line in the GROUP BY clause, and see if that solves the problem.

answered Nov 04 '13 at 20:58

mti2935

11,465
3
29
33

this fails also still getting this error SQL Error: ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function" – Luke14 Nov 04 '13 at 21:00
1

OK, I think you're getting closer now. I think the problem is MAX(SUM(...)). You can't nest the aggregate functions like that. I'm not sure exactly what you are trying to do here, but try it with just one - i.e just MAX(...) or just SUM(...). – mti2935 Nov 04 '13 at 21:02

score 0 · Answer 3 · answered Nov 04 '13 at 21:05

If you want the max value, you have to wrap it all up in subquery, take a look:

SELECT c.customer_first_name, c.customer_last_name, SUM(cost_line)
FROM customers c LEFT OUTER JOIN orders o USING(customer_numb)
LEFT OUTER JOIN order_lines l USING (order_numb)
GROUP BY c.customer_first_name, c.customer_last_name
HAVING SUM(cost_line) = (
  SELECT MAX(sum_cost_line)
    FROM
    (SELECT SUM(cost_line) sum_cost_line
      FROM customers c LEFT OUTER JOIN orders o USING(customer_numb)
        LEFT OUTER JOIN order_lines l USING (order_numb)
      GROUP BY c.customer_first_name, c.customer_last_name) a
  )
ORDER BY customer_numb;

Edit if you are using Oracle, you can make it simpler:

SELECT c.customer_first_name, c.customer_last_name, SUM(cost_line)
    FROM customers c LEFT OUTER JOIN orders o USING(customer_numb)
    LEFT OUTER JOIN order_lines l USING (order_numb)
    GROUP BY c.customer_first_name, c.customer_last_name
    HAVING SUM(cost_line) = (
      SELECT MAX(SUM(cost_line))
          FROM customers c LEFT OUTER JOIN orders o USING(customer_numb)
            LEFT OUTER JOIN order_lines l USING (order_numb)
          GROUP BY c.customer_first_name, c.customer_last_name
      )
    ORDER BY customer_numb;

SQL group by "column name"

3 Answers3

Linked