AVG function is giving wrong values in sql

Question

I have 3 tables region, nation, supplier. I have a total of 3000 records in my supplier table. I want to find average account balance of specific nation. I tried AVG (counted) value of s_acctbal but it is giving me 3000 as AVG value , no matter how I change to other nations rather than CHINA. I found similar questions but those methods did not solved my issue. How can I get average account balance of specific nation?

CREATE TABLE REGION(

R_REGIONKEY NUMBER(12)  NOT NULL,
R_NAME      CHAR(25)    NOT NULL,
R_COMMENT   VARCHAR(152)    NOT NULL,
CONSTRAINT REGION_PKEY PRIMARY KEY(R_REGIONKEY),
CONSTRAINT REGION_CHECK1 CHECK(R_REGIONKEY >= 0) );

CREATE TABLE NATION(
N_NATIONKEY NUMBER(12)  NOT NULL,
N_NAME      CHAR(25)    NOT NULL,
N_REGIONKEY NUMBER(12)  NOT NULL,
N_COMMENT   VARCHAR(152)    NOT NULL,
CONSTRAINT NATION_PKEY PRIMARY KEY (N_NATIONKEY),
CONSTRAINT NATION_FKEY1 FOREIGN KEY (N_REGIONKEY)
REFERENCES REGION(R_REGIONKEY),
CONSTRAINT NATION_CHECK1 CHECK(N_NATIONKEY >= 0) );

CREATE TABLE PART(
P_PARTKEY   NUMBER(12)  NOT NULL,
P_NAME      VARCHAR(55) NOT NULL,
P_MFGR      VARCHAR(25) NOT NULL,
P_BRAND     CHAR(10)    NOT NULL,
P_TYPE      VARCHAR(25) NOT NULL,
P_SIZE      NUMBER(12)  NOT NULL,
P_CONTAINER     CHAR(10)    NOT NULL,
P_RETAILPRICE   NUMBER(12,2)    NOT NULL,
P_COMMENT   VARCHAR(23) NOT NULL,
CONSTRAINT PART_PEKEY PRIMARY KEY (P_PARTKEY),
CONSTRAINT PART_CHECK1 CHECK(P_PARTKEY >= 0),
CONSTRAINT PART_CHECK2 CHECK(P_SIZE >= 0),
CONSTRAINT PART_CHECK3 CHECK(P_RETAILPRICE >= 0) );

CREATE TABLE SUPPLIER(
S_SUPPKEY   NUMBER(12)  NOT NULL, 
S_NAME      CHAR(25)    NOT NULL,
S_ADDRESS   VARCHAR(40) NOT NULL,
S_NATIONKEY NUMBER(12)  NOT NULL,
S_PHONE     CHAR(15)    NOT NULL,
S_ACCTBAL   NUMBER(12,2)    NOT NULL,
S_COMMENT   VARCHAR(101)    NOT NULL,
CONSTRAINT SUPPLIER_PKEY PRIMARY KEY (S_SUPPKEY),
CONSTRAINT SUPPLIER_FKEY1 FOREIGN kEY (S_NATIONKEY)
REFERENCES NATION(N_NATIONKEY),
CONSTRAINT SUPPLIER_CHECK1 CHECK(S_SUPPKEY >= 0) );

This is total records;

 SQL> select count(*) from supplier;
    
      COUNT(*)
    ----------
          3000

and this is my sql code;

select n_name, s_name, s_acctbal, AVG(counted)
from (select Count(s_acctbal) AS counted from supplier), supplier, nation, region
where s_nationkey = n_nationkey
and r_regionkey = n_regionkey
and n_name = 'CHINA'
group by n_name, s_name, s_acctbal;

Some of the output I'm getting (I did not paste all because there are 145 rows);

N_NAME            S_NAME             S_ACCTBAL AVG(COUNTED)
------------------------- ------------------------- ---------- ------------
CHINA             Supplier#000001610           3120.6          3000
CHINA             Supplier#000001674            340.14         3000
CHINA             Supplier#000001876           9804.43         3000
CHINA             Supplier#000001145           8752.68         3000
CHINA             Supplier#000001168           1154.17         3000
CHINA             Supplier#000001249            999.08         3000
CHINA             Supplier#000000793           7044.94         3000
CHINA             Supplier#000001106           3079.79         3000
CHINA             Supplier#000001117           3620.41         3000
CHINA             Supplier#000000027           1887.62         3000
CHINA             Supplier#000000041           6942.67         3000

N_NAME            S_NAME             S_ACCTBAL AVG(COUNTED)
------------------------- ------------------------- ---------- ------------
CHINA             Supplier#000000082           -724.31         3000
CHINA             Supplier#000000395            688.37         3000
CHINA             Supplier#000000499           2518.34         3000
CHINA             Supplier#000002290           8573.93         3000
CHINA             Supplier#000002484           8797.54         3000
CHINA             Supplier#000002664           1095.86         3000
CHINA             Supplier#000002761           3448.45         3000
CHINA             Supplier#000002171           8624.28         3000
CHINA             Supplier#000002231           5973.38         3000
CHINA             Supplier#000001378           7119.9          3000
CHINA             Supplier#000001557           9059.38         3000

N_NAME            S_NAME             S_ACCTBAL AVG(COUNTED)
------------------------- ------------------------- ---------- ------------
CHINA             Supplier#000001843           8670.08         3000
CHINA             Supplier#000002861           6821.08         3000

145 rows selected.

Answer 1

The problem as code show is: You want the average of counted, the AVG function required a collection of values to calculate their average, but you send only one value which is the count of counted.

Try this code:

select n_name, s_name, s_acctbal, 
AVG(counted) from (select s_acctbal AS counted from supplier), 
supplier, nation, region 
where s_nationkey = n_nationkey and r_regionkey = n_regionkey and n_name = 'CHINA' 
group by n_name, s_name, s_acctbal;

The AVG function now will receive all values of s_acctbal columns and calculate their average.

I don't know if there are another errors.

Answer 2

First off you don't need the REGION table here unless there's something missing from your question.

Second, you need to learn how aggregate functions like AVG, SUM, COUNT and others require a GROUP BY clause.

Third, your query attempt implies that you don't fully understand table joins. Take the time to learn them because if you don't know joins you don't know SQL, period. Practice them until they're second nature.

For your query you need just these tables:

• NATION, to get the nation key for China
• SUPPLIER, to get the supplier name and their account balance

Following the general approach of your query attempt, the query would look like this:

select n_name, s_name, AVG(s_acctbal)
from nation, supplier
where n_nationkey = s_nationkey
  and n_name = 'CHINA'
group by n_name, s_name

Aggregating: note the group by n_name, s_name. As a general rule, when your SELECT has an aggregate function like AVG(something) you need to group by every value in the select list that isn't an aggregate function. Your select list has this:

• n_name - not an aggregate function
• s_name - not an aggregate function
• AVG(s_acctbal) - is an aggregate function

So, group by n_name, s_name.

Joining: Just a bit on this because you need deeper study. The query in my answer joins by using this part of the syntax:

from nation, supplier
where n_nationkey = s_nationkey

It pulls from the nation and supplier tables. But how do you define which nation and supplier records go together? With the join, which in this case is defined in the WHERE clause (you can also use a JOIN clause but get to that when you're ready): where n_nationkey = s_nationkey.