Issues with counting the Quantity of a Value

by Andrew Van   Last Updated May 16, 2019 02:06 AM

im having some issues with inserting values into a table


    CREATE TABLE COMPANY_AND_SUPPLIERS (

        COMPANY_NAME VARCHAR (40) NOT NULL DEFAULT 'EMPTY',
        PRODUCT_NAME VARCHAR(40) NOT NULL DEFAULT 'EMPTY' ,
        TOTAL_PRODUCTS VARCHAR(40) NOT NULL DEFAULT 'EMPTY',
        CONSTRAINT SUPPLIER_PKEY PRIMARY KEY(COMPANY_NAME) ,
        CONSTRAINT SUPPLIER_FKEY FOREIGN KEY (COMPANY_NAME) REFERENCES SUPPLIER(COMPANY_NAME)

        );

        INSERT INTO COMPANY_AND_SUPPLIERS (COMPANY_NAME)
        SELECT COMPANY_NAME
        FROM SUPPLIER;

        INSERT INTO COMPANY_AND_SUPPLIERS(PRODUCT_NAME)
        SELECT PRODUCT_NAME
        FROM PRODUCT;

        UPDATE COMPANY_AND_SUPPLIERS
        SET TOTAL_PRODUCTS = (SELECT COUNT(*) AS TOTALPRODUCTS
        FROM PRODUCT);


The given question is "Create a new relational table to store information about the company names of all suppliers and the total number of products supplied by each supplier. Enforce, the appropriate consistency constraints on the new table. Next, copy into the new table information about the company names of all suppliers and the total number of products supplied by each supplier. "

My understanding is that when Working The Query returns the number of products sold by each Company, Examle Company X sells 10 of Product Y. So far my qurery prints the 3 Columns but PRODUCT_NAME is 0 and TOTALPRODUCTS is 1. Im wondering what is wrong with my count? The expected results should be a total of 9 items equal to a quantity of 29.

Another issue i am also having is a foreign key constraint when inserting these values

        INSERT INTO COMPANY_AND_SUPPLIERS(PRODUCT_NAME)
        SELECT PRODUCT_NAME
        FROM PRODUCT;

Below are the tables im using

CREATE TABLE SUPPLIER
(
    COMPANY_NAME    VARCHAR(40) NOT NULL,
    CONTACT_NAME    VARCHAR(30),
    CONTACT_TITLE   VARCHAR(30),
    ADDRESS         VARCHAR(60),
    CITY        VARCHAR(15),
    REGION      VARCHAR(15),
    POSTAL_CODE     VARCHAR(10),
    COUNTRY         VARCHAR(15),
    PHONE       VARCHAR(24),
    FAX         VARCHAR(24),
    HOME_PAGE       VARCHAR(500),
    CONSTRAINT PK_SUPPLIER PRIMARY KEY (COMPANY_NAME)  
);
CREATE TABLE PRODUCT
(
    PRODUCT_NAME    VARCHAR(40)     NOT NULL,
    SUPPLIER_NAME   VARCHAR(40)     NOT NULL,
    CATEGORY_NAME   VARCHAR(30) NOT NULL,
    QUANTITY_PER_UNIT   VARCHAR(20),
    UNIT_PRICE      DECIMAL(10,2)   NOT NULL    DEFAULT 0,
    UNITS_IN_STOCK  DECIMAL(9)  NOT NULL    DEFAULT 0,
    UNITS_ON_ORDER  DECIMAL(9)  NOT NULL    DEFAULT 0, 
    REORDER_LEVEL   DECIMAL(9)  NOT NULL    DEFAULT 0,
    DISCONTINUED    CHAR(1)     NOT NULL    DEFAULT 'N',
    CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_NAME),
    CONSTRAINT FK_CATEGORY_NAME FOREIGN KEY (CATEGORY_NAME) REFERENCES CATEGORY(CATEGORY_NAME),
    CONSTRAINT FK_SUPPLIER_NAME FOREIGN KEY (SUPPLIER_NAME) REFERENCES SUPPLIER(COMPANY_NAME),
    CONSTRAINT CK_PRODUCT_UNIT_PRICE CHECK (UNIT_PRICE >= 0),
    CONSTRAINT CK_PRODUCT_UNITS_IN_STOCK CHECK (UNITS_IN_STOCK >= 0),
    CONSTRAINT CK_PRODUCT_UNITS_ON_ORDER CHECK (UNITS_ON_ORDER >= 0),
    CONSTRAINT CK_PRODUCT_REORDER_LEVEL CHECK (REORDER_LEVEL >= 0),
    CONSTRAINT CK_PRODUCT_DISCONTINUED CHECK (DISCONTINUED in ('Y','N'))
);

If anyone can help it would be much appreciated! :)

Tags : mysql count


Related Questions


Updated June 13, 2015 00:02 AM

Updated September 04, 2017 04:06 AM

Updated October 24, 2017 06:06 AM

Updated February 29, 2016 01:02 AM

Updated April 25, 2016 08:02 AM