About ORACLE SQL Query

Question

I have a three tables and I merged the tables like below. I want to print the name with an average of nine points for the title and reduce the year by ten.

TITLE	YEAR	POINT	NAME
A	1999	9	K
A	1999	9	L
C	1997	7	M

For this, I wrote the following query but the query fails. What query should I write?

SELECT k.title, k.year, AVG(point), m.name
  FROM Table1 k 
  JOIN Table2 l ON (k.title=l.title) 
  JOIN Table3 m ON (l.year=m.year) 
 GROUP BY title;

Answer 1

if you connected the tables correctly, then it's an issue with the GROUP BY clause. If you are using aggregated functions such as (AVG,SUM,COUNT) in the Select clause, all other columns included in the select clause must be added in the group by clause. this should work:

SELECT k.TITLE, k.YEAR, AVG(POINT), m.NAME 
FROM Table1 k JOIN Table2 l ON (k.TITLE=l.TITLE) JOIN Table3 m ON (l.YEAR=m.YEAR) 
GROUP BY (k.TITLE, k.YEAR,m.NAME);

Edit:

If you want to substract years from the Select clause you can use ADD_MONTHS function like this:

SELECT k.TITLE, ADD_MONTHS( TRUNC(k.YEAR), -12*10 ), AVG(POINT), m.NAME 
FROM Table1 k JOIN Table2 l ON (k.TITLE=l.TITLE) JOIN Table3 m ON (l.YEAR=m.YEAR) 
GROUP BY (k.TITLE, ADD_MONTHS( TRUNC(k.YEAR), -12*10 ),m.NAME);

you also can add WHERE clause to your query and specify some conditions, the WHERE clause should be added before the group by clause and after the FROM clause.

you basically can replace any DATE data type column with ADD_MONTHS( TRUNC(yourcolumn), -12*10 ) replacing (yourcolumn) with the column you want to substract years from.

best of luck to you.