SQL Statement to create a table as a result of a count operation?

Let’s say you got a table like this

id   terms    
1    a       
2    c       
3    a       
4    b       
5    b       
6    a       
7    a
8    b
9    b
10   b        

and you want to end up with a report like this;

terms  count
a      4
b      5
c      1

So you run this over the first table

SELECT terms, COUNT( id) AS count 
    FROM table 
GROUP BY terms 
ORDER BY terms DESC

So far so good.

But the above SQL statment puts the report view on the browser. Well, I want to save that data into a SQL.

So, what SQL command do I need to insert the results of that report into a table?

Assume that you already created a table called reports with this;

create table reports (terms varchar(500), count (int))

Let’s assume that the reports table is empty and we just want to populate it with the following view – with a one-liner. The question I’m asking is how?

  terms  count
    a      4
    b      5
    c      1

Answer

As simple as that:

INSERT INTO reports
SELECT terms, COUNT( id) AS count 
FROM table 
GROUP BY terms 
ORDER BY terms DESC