CODEWITHSUNDEEP
mysqldatabase
Narayana J
Narayana J

Reputation: 307

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!

Upvotes: 1

Views: 708

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43594

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 and without creating duplicate rows:

INSERT INTO derived (Category, Category_count)
SELECT Category, COUNT(*) AS Category_count 
FROM original o 
WHERE NOT EXISTS (SELECT * FROM derived d WHERE d.Category = o.Category)
GROUP BY Category

Use the following to update the rows already exists:

UPDATE derived LEFT JOIN (
    SELECT Category, COUNT(*) AS Category_count 
    FROM original 
    GROUP BY Category
)x ON derived.Category = x.Category
SET derived.Category_count = x.Category_count

Upvotes: 1

Prasanna Kumar J
Prasanna Kumar J

Reputation: 1618

Select catoregy,count(id)as category_count into derived from original group by catoregy

Upvotes: 0

Related Questions