2

I have little problem with my SQL query. I want to get my CUSTOMERS table id, name, surname, and all money they spent on my shop i've created.

SELECT o.CUSTOMER_ID AS "ID", c.name AS "Name", c.SURNAME AS "Surname",
       (SELECT op.AMOUNT * p.PRICE 
          FROM PRODUCTS p 
         WHERE p.id = op.PRODUCT_ID) AS "Money spent"
  FROM ORDERS o 
       LEFT JOIN CUSTOMERS c ON c.ID = o.CUSTOMER_ID 
       LEFT JOIN ORDERS_PRODUCTS op ON op.ORDER_ID = o.id
 GROUP BY o.CUSTOMER_ID;

And i have error message like this:

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Error at Line: 1 Column: 31

I have to say, that i have to use GroupBy clause, because i have this in my school project requirements. And this is my base diagram, if it could help you. http://i61.tinypic.com/2d1okut.jpg

pnuts
  • 58,317
  • 11
  • 87
  • 139
wojcienty
  • 313
  • 1
  • 4
  • 14

1 Answers1

5

As you can see in the ORACLE docs

SelectItems in the SelectExpression with a GROUP BY clause must contain only aggregates or grouping columns.

This means that if you only group by o.CUSTOMER_ID, all the other fields in the select list must be aggregate functions (like COUNT, MAX, etc.).

In the case of fields that repeat values in each group (as name and surname) you should include them in the GORUP BY clause.

To include the sum of money spent, you could add another LEFT JOIN with PRODUCTS and select SUM(op.amount*p.price) without a subquery.

That would be

SELECT o.CUSTOMER_ID AS "ID", c.name AS "Name", c.SURNAME AS "Surname",
       SUM(op.AMOUNT*p.PRICE) AS "Money spent"
  FROM ORDERS o 
       LEFT JOIN CUSTOMERS c ON c.ID = o.CUSTOMER_ID 
       LEFT JOIN ORDERS_PRODUCTS op ON op.ORDER_ID = o.id
       LEFT JOIN PRODUCTS p ON p.id = op.PRODUCT_ID
 GROUP BY o.CUSTOMER_ID, c.name AS "Name", c.SURNAME
 ORDER BY o.CUSTOMER_ID, c.name AS "Name", c.SURNAME;

Remember always to define the sort order of your queries, otherwise it will be undefined.

1010
  • 1,779
  • 17
  • 27