CODEWITHSUNDEEP
mysqlselectsyntaxinsertalias
Arjun Mehta
Arjun Mehta

Reputation: 2542

MYSQL Save Query Results into Another Table

I'm looking to store the results of a query that returns a list of all the unique values in a particular column, and how many times they are repeated:

SELECT col_name, COUNT(*) AS 'total' FROM dataTable GROUP BY col_name;

This returns something like:

col_name

total

It's working great and exactly what I need. But I need to store the results into another table (eg. countTable) so I don't have to do the query every time, can index it... etc.

Let's say countTable has the exact same structure as the results here. Two columns: name and count of the same types as the original result.

This is going to probably raise some laughs, but currently I feel like this is close:

INSERT INTO countTable (name, count) SELECT col_name, total FROM 
(SELECT col_name, COUNT(*) AS 'total' FROM dataTable GROUP BY col_name);

But I get an error: ERROR 1248 (42000): Every derived table must have its own alias

I have no idea how to wrap this query right, or if I'm supposed to use another AS somewhere?

Any help greatly appreciated!! Thanks

Upvotes: 1

Views: 3686

Answers (2)

Arjun Mehta
Arjun Mehta

Reputation: 2542

AH! Turns out I was very close in my original query:

INSERT INTO countTable (name, count) SELECT col_name, total FROM 
(SELECT col_name, COUNT(*) AS 'total' FROM dataTable GROUP BY col_name);

I DID need to add an extra AS at the very end. Every derived table MUST indeed have its own alias :)

INSERT INTO countTable (name, count) SELECT col_name, total FROM 
(SELECT col_name, COUNT(*) AS 'total' FROM dataTable GROUP BY col_name) AS count;

But it would seem I'm wrapping it unnecessarily as evidenced by spaceman's answer.

Upvotes: 0

mcriecken
mcriecken

Reputation: 3297

You can do a SELECT right after the INSERT statement. As long as what you are selecting matches the values you want to insert (in this case name and count). You can test what will be inserted into your countTable by just running the select statement by itself.

INSERT INTO countTable (name, count)
SELECT col_name, COUNT(*) AS 'total' 
FROM dataTable 
GROUP BY col_name;

Upvotes: 2

Related Questions