BJJ BA BJJ BA - 19 days ago 5
SQL Question

sql - How to calculate an average from a query

I've been using SQL for about a week at my first full time job, and I'm trying to calculate some statistics from a query where I've combined columns from separate tables.

Specifically, I'm trying to calculate an average from a combined table, where I have applied filters (or constraints? I'm not clear on the SQL lingo).

From doing research on Google, I learnt how to calculate an average:

SELECT AVG(column_name) FROM table_name


The problem I'm having is that this only seems to work with existing tables in the database, not with new queries I have created.

A simplified version of my code is as follows:

SELECT Animal_Facts.Animal_Name, Animal_Facts.Prev_Reg_Amount,
Names.Given_Name, Animal_Class.Class_Description

FROM Names
Inner Join Animal_Facts on Names.Name_Key = Animal_Facts.Name_Key
Inner Join Animal_Class on Animal_Facts.Class_Key = Animal_Class.Class_Key


This query creates combines four columns from three tables, where Class_Description describes whether the animal is desexed, microchipped, owned by a pensioner etc, and Pre_Reg_Amount is the registration fee paid.

I want to find the average fee paid by pensioners, so I included the following line of code to filter the table:

and Animal_Class.Class_Description like ('%pensioner%')


And then to calculate the average I add:

SELECT AVG(Animal_Facts.Prev_Reg_Amount) from Animal_Facts


So my total code is:

SELECT Animal_Facts.Animal_Name, Animal_Facts.Prev_Reg_Amount,
Names.Given_Name, Animal_Class.Class_Description

FROM Names
Inner Join Animal_Facts on Names.Name_Key = Animal_Facts.Name_Key
Inner Join Animal_Class on Animal_Facts.Class_Key = Animal_Class.Class_Key

and Animal_Class.Class_Description like ('%pensioner%')

SELECT AVG(Animal_Facts.Prev_Reg_Amount) from Animal_Facts


Now the problem is, after checking this calculation in Excel, I'm not actually getting the average of the pensioner data, but the average of all the data. Is there a way to calculate averages (and other statistics) directly from my created table in SQL?

Note: I am able to calculate all these statistics by exporting the data to Excel, but it is much more time consuming. I'd much rather learn how to do this within SQL.

Answer
SELECT AVG(af.Prev_Reg_Amount)
FROM
    Animal_Facts af
    INNER JOIN Animal_Class ac
    ON af.Class_Key = ac.Class_Key
    AND Class_Description LIKE ('%pensioner%')