Joining 2 tables in MySql example code provided. Looking for assistance

user_19902053 0
Viewed 38

The meaning of this question is to gather some answers for a task i am doing. I am trying to display the amount of products a vendor is supplying along with the vendor code next to it. I keep getting errored when trying to run the code listed below in Mysql workbench.

Write a SQL statement to list V_CODE and V_NAME along with the number of products they supply (column name as ‘NUM_PRODS’) and the average price of product they supply (column name as ‘AVG_PROD_PRICE’).

Below is what i have currently but cant seem to get the V_CODE to ever display.

    SELECT VENDOR.VENDOR_CODE, VENDOR.V_NAME, count(distinct PRODUCT.P_CODE) as NUM_PRODS
    FROM VENDOR
    JOIN PRODUCT on VENDOR.V_CODE = PRODUCT.V_CODE
    GROUP BY VENDOR.V_NAME;

Just looking for some insight into how come I cannot display the VENDOR.V_CODE throught the SELECT statement.

Thanks alot for your help!

mysql
2 Answers

VENDOR_CODE is not shown because you are grouping by VENDOR.V_NAME. This means that there can be more than one value ​​in VENDOR_CODE. This is not possible in SQL. For example, you could use MAX(VENDOR.VENDOR_CODE) to get the highest VENDOR_CODE.

user_195917970

VENDOR_CODE is not shown because you are grouping by VENDOR.V_NAME. This means that there can be more than one value ​​in VENDOR_CODE. This is not possible in SQL. For example, you could use MAX(VENDOR.VENDOR_CODE) to get the highest VENDOR_CODE.

A list of possible functions with examples can be found here

user_144438260
Related