sql 코드 문제, 하나의 행만 반환

sql 코드 문제, 하나의 행만 반환

이 sql 쿼리에 문제가 있습니다.

SELECT c.country_code, co.country_name, COUNT(c.city_name)
FROM Country co JOIN City c USING (country_code)
ORDER BY COUNT(c.city_name)
GROUP BY c.country_code

이것은 문장에 의한 그룹 근처의 오류를 반환하는 것입니다.제 질문이 잘못된 부분을 이해하지 못하고 있습니다.제가 방금 select and from statement를 입력한 곳에서 테스트를 해보니 각 도시의 총량이 있는 모든 국가를 구하려고 하는데 여전히 캐나다만 반환되고 44개의 도시가 있다고 되어있는데, 이는 명백히 잘못된 것입니다.쿼리가 잘못된 이유를 설명해 줄 수 있는 사람이 있습니까?

CREATE TABLE Country ( 
        country_code VARCHAR(50) NOT NULL, /* country abbreviation */
        country_name VARCHAR(50) NOT NULL, /* country name*/
        gdp INT UNSIGNED NOT NULL, /*-- groSEELss domestic product*/
        inflation DOUBLE NOT NULL, /*-- inflation rate */
        PRIMARY KEY (country_code)
    );
    
    INSERT INTO Country VALUES ('US', 'United States', 37000, 3.8);
    INSERT INTO Country VALUES ('MEX', 'Mexico', 27000, 2.8);
    INSERT INTO Country VALUES ('CAN', 'Canada', 34000, 3.6);
    
    INSERT INTO Country VALUES ('CHN', 'China', 70000, 0.3);
    INSERT INTO Country VALUES ('GER', 'Germany', 67000, 0.9);
    INSERT INTO Country VALUES ('SPA', 'Spain', 37000, 2.5);
    INSERT INTO Country VALUES ('FRA', 'France', 43000, 1.2);
    INSERT INTO Country VALUES ('ITA', 'Italy', 50000, 1.5);
    INSERT INTO Country VALUES ('IND', 'India', 56000, 3.1);
    INSERT INTO Country VALUES ('MON', 'Mongolia', 32000, 2.5);
    INSERT INTO Country VALUES ('RUS', 'Russia', 74000, 3.5);
    
    
    /* Table describes the provinces each country has */
    
    
    CREATE TABLE Province (
        province_name VARCHAR(50) NOT NULL, /* province name */
        country_code VARCHAR(50) NOT NULL,  /* country abbreviation */
        area INT UNSIGNED, /* total province area in km^2 */
        PRIMARY KEY (province_name, country_code),
        FOREIGN KEY (country_code) REFERENCES Country (country_code)
    );
    
    INSERT INTO Province VALUES ('Washington', 'US', 400000);
    INSERT INTO Province VALUES ('Oregon', 'US', 300000);
    INSERT INTO Province VALUES ('California', 'US', 200000);
    
    INSERT INTO Province VALUES ('Jalisco', 'MEX', 230000);
    INSERT INTO Province VALUES ('Nuevo Leon', 'MEX', 130000);
    INSERT INTO Province VALUES ('Yucatan', 'MEX', 430000);
    
    INSERT INTO Province VALUES ('British Columbia', 'CAN', 530000);
    INSERT INTO Province VALUES ('Ontario', 'CAN', 50000);
    INSERT INTO Province VALUES ('Alberta', 'CAN', 48000);
    
    
    INSERT INTO Province VALUES ('Guizhou', 'CHN', 428000);
    INSERT INTO Province VALUES ('Shandong', 'CHN', 58000);
    INSERT INTO Province VALUES ('Hunan', 'CHN', 89000);
    
    INSERT INTO Province VALUES ('Bavaria', 'GER', 708000);
    INSERT INTO Province VALUES ('Saxony', 'GER', 41000);
    INSERT INTO Province VALUES ('Hessen', 'GER', 480000);
    
    INSERT INTO Province VALUES ('Catalonia', 'SPA', 485000);
    INSERT INTO Province VALUES ('Galicia', 'SPA', 85000);
    INSERT INTO Province VALUES ('Castilla La Mancha', 'SPA', 67000);
    
    
    
    
    /* Table describes the cities in the different provinces */
    
    CREATE TABLE City (
        city_name VARCHAR(50) NOT NULL, /*name of city */
        province_name VARCHAR(50) NOT NULL, /* name of province */
        country_code VARCHAR(50) NOT NULL, /* country abbreviation */
        population INT UNSIGNED, /* total population */
        PRIMARY KEY (city_name, province_name, country_code),
        FOREIGN KEY (province_name) REFERENCES Province (province_name),
        FOREIGN KEY (country_code) REFERENCES Province (country_code)
    );
    
    INSERT INTO City VALUES ('Seattle', 'Washington', 'US', 23030);
    INSERT INTO City VALUES ('Redmond', 'Washington', 'US', 2400);
    INSERT INTO City VALUES ('Kirkland', 'Washington', 'US', 22350);
    INSERT INTO City VALUES ('Portland', 'Oregon', 'US', 33040);
    INSERT INTO City VALUES ('Sunriver', 'Oregon', 'US', 4300);
    INSERT INTO City VALUES ('Bend', 'Oregon', 'US', 54300);
    INSERT INTO City VALUES ('San Francisco', 'California', 'US', 6300);
    INSERT INTO City VALUES ('San Diego', 'California', 'US', 2300);
    INSERT INTO City VALUES ('Los Angelos', 'California', 'US', 7300);
    
    INSERT INTO City VALUES ('Puerto Vallarta', 'Jalisco', 'MEX', 4800);
    INSERT INTO City VALUES ('Zapopan', 'Jalisco', 'MEX', 1900);
    INSERT INTO City VALUES ('Monterrey', 'Nuevo Leon', 'MEX', 9200);
    INSERT INTO City VALUES ('Santa Catarina', 'Nuevo Leon', 'MEX', 470);
    INSERT INTO City VALUES ('Guadalupe', 'Nuevo Leon', 'MEX', 500);
    INSERT INTO City VALUES ('Merida', 'Yucatan', 'MEX', 2000);
    INSERT INTO City VALUES ('Valladolid', 'Yucatan', 'MEX', 480);
    INSERT INTO City VALUES ('Izamal', 'Yucatan', 'MEX', 200);
    
   
    INSERT INTO City VALUES ('Victoria', 'British Columbia', 'CAN', 2200);
    INSERT INTO City VALUES ('London', 'Ontario', 'CAN', 2300);
    INSERT INTO City VALUES ('Toronto', 'Ontario', 'CAN', 3500);
    INSERT INTO City VALUES ('Ottawa', 'Ontario', 'CAN', 8000);
    INSERT INTO City VALUES ('Calgary', 'Alberta', 'CAN', 3040);
    INSERT INTO City VALUES ('Red Deer', 'Alberta', 'CAN', 5700);
    INSERT INTO City VALUES ('Edmonton', 'Alberta', 'CAN', 300);
    
    INSERT INTO City VALUES ('Guiyang', 'Guizhou', 'CHN', 40000);
    INSERT INTO City VALUES ('Anshun', 'Guizhou', 'CHN', 23000);
    INSERT INTO City VALUES ('Zunyi', 'Guizhou', 'CHN', 10000);
    INSERT INTO City VALUES ('Qingdao', 'Shandong', 'CHN', 100000);
    INSERT INTO City VALUES ('Jinan', 'Shandong', 'CHN', 9000);
    INSERT INTO City VALUES ('Yantai', 'Shandong', 'CHN', 70000);
    INSERT INTO City VALUES ('Changsha', 'Hunan', 'CHN', 100000);
    INSERT INTO City VALUES ('Yueyang', 'Hunan', 'CHN', 5000);
    INSERT INTO City VALUES ('Changde', 'Hunan', 'CHN', 57700);

언급URL : https://stackoverflow.com/questions/70051141/issue-with-sql-code-only-returning-one-row