AVG function is giving wrong values in sql-CodePudding

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. Can anyone advise how can I get average account balance of specific nation? Thanks in advanced!

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;

output im getting;

CodePudding user response：

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.

CodePudding user response：

First, don't send images. We cannot show images.

Second, 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, because I can't see the images in the question.

Reformat your code without images if this answer doesn't solve the problem.