CODEWITHSUNDEEP
sqldatabase
user2053184
user2053184

Reputation:

SQL not a single group group function error

I am having a hard time getting my oracle developer query to output correctly. When I have the avg function in it, it gives me not a single group group error. When I take it out it works fine. I have tried using group by instead of order by but then it tells me that its not a group by expression. 

SELECT LGBRAND.BRAND_ID, LGBRAND.BRAND_NAME, AVG(LGPRODUCT.PROD_PRICE)AS AVGER
FROM LGPRODUCT, LGBRAND
WHERE LGPRODUCT.BRAND_ID = LGBRAND.BRAND_ID
ORDER BY BRAND_NAME;

Upvotes: 7

Views: 50027

Answers (4)

Ramu Vemula
Ramu Vemula

Reputation: 197

For your newly added fields, you didn't used group by, hence you are getting this error, so by including that newly added fields to group by you will resolve this error and group all the data that would have to be aggregated.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69594

SELECT LGBRAND.BRAND_ID, LGBRAND.BRAND_NAME, AVG(LGPRODUCT.PROD_PRICE)AS AVGER
FROM LGPRODUCT, LGBRAND
WHERE LGPRODUCT.BRAND_ID = LGBRAND.BRAND_ID
GROUP BY LGBRAND.BRAND_ID, LGBRAND.BRAND_NAME
ORDER BY BRAND_NAME;

OR

SELECT LGBRAND.BRAND_ID, LGBRAND.BRAND_NAME, AVG(LGPRODUCT.PROD_PRICE)AS AVGER
FROM LGPRODUCT INNER JOIN LGBRAND
ON LGPRODUCT.BRAND_ID = LGBRAND.BRAND_ID
GROUP BY LGBRAND.BRAND_ID, LGBRAND.BRAND_NAME
ORDER BY BRAND_NAME;

Note
Whenever an aggregate function(SUM, COUNT, AVG, MIN, MAX, ..) is used in SELECT all the other column in that SELECT that are not contained in any aggregate function must come in GROUP BY clause

Upvotes: 2

Jorge Campos
Jorge Campos

Reputation: 23391

You are using an AGGREGATION function and in order to do so you have to group all the data that would have to be aggregated.

SELECT LGBRAND.BRAND_ID, LGBRAND.BRAND_NAME, AVG(LGPRODUCT.PROD_PRICE) AS AVGER
  FROM LGPRODUCT, LGBRAND
 WHERE LGPRODUCT.BRAND_ID = LGBRAND.BRAND_ID
group by LGBRAND.BRAND_ID, LGBRAND.BRAND_NAME -- This line here
ORDER BY BRAND_NAME;

Upvotes: 0

Andrew
Andrew

Reputation: 8758

When you include an aggregate function (like avg, sum) in your query, you must group by all columns you aren't aggregating.

SELECT LGBRAND.BRAND_ID, LGBRAND.BRAND_NAME, AVG(LGPRODUCT.PROD_PRICE)AS AVGER
FROM LGPRODUCT, LGBRAND
WHERE LGPRODUCT.BRAND_ID = LGBRAND.BRAND_ID
GROUP BY
LGBRAND.BRAND_ID,
LGBRAND.BRAND_NAME
ORDER BY BRAND_NAME

Upvotes: 15

Related Questions