SQL is not a single function of a group function

Question

I find it difficult to get the correct output from my oracle developer request. When I have an avg function, it does not give me a single group error. When I take it out, it works great. I tried to use a group instead of order, but then he tells me that this is not an expression group.

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; 

Answer 1

When you include an aggregate function in your query (for example, avg, sum), you should group all the columns that you are not 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 

Answer 2

 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 other columns are that SELECTs that are not contained in any aggregate function should be included in the GROUP BY clause

Answer 3

You use the AGGREGATION function, and for this you need to group all the data that will need 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; 

Answer 4

You did not use a group for newly added fields, so you get this error, therefore, by including new added fields in it, you will resolve this error and group all the data that should be aggregated.