Narayana J Narayana J - 12 days ago 6
MySQL Question

Creating a new table using multiple SELECTs

Please see the image below:

enter image description here

Given the

original
table, I need to create the
derived
table in MySQL on the server.

With
CREATE TABLE ... AS SELECT DISTINCT...
I am able to create the
derived
table with the
Category
column, but trying in vain to create the
Category_count
column. Can you kindly point out how to solve this?

Not very comfortable with MySQL or SQL even, hence the request.

Many thanks in advance!

Answer

Use the following if the table does not exists

CREATE TABLE derived AS
SELECT Category, COUNT(*) AS Category_count 
FROM original 
GROUP BY Category

Use the following if the table already exists

INSERT INTO derived (Category, Category_count)
SELECT Category, COUNT(*) AS Category_count 
FROM original 
GROUP BY Category  
Comments