Why am I getting a syntax error on this INTO command?-CodePudding

UPDATE: It helps if you put the data INTO your table first. I adjusted the code and it works!

I am working in SQL and I want to count unique titles within a table. The top code is a query I wrote to count by employee number, select the title column from the unique_titles table, group by the title, and create a new table in descending order. The bottom code is, obviously a syntax error. I'm not sure this code is correct but the INTO statement looks correct to me:

SELECT COUNT (unique_titles.emp.no), unique_titles.title
FROM unique_titles
GROUP BY unique_titles.title
INTO retiring_titles
ORDER BY DESC;

LINE 4: INTO retiring_titles
        ^
SQL state: 42601
Character: 107```

CodePudding user response：

You can try this:

SELECT COUNT(unique_titles.id)
INTO retiring_titles
FROM unique_titles
GROUP BY unique_titles.title
ORDER BY DESC;

CodePudding user response：

If I understand correctly, CREATE TABLE AS SELECT can help you.

It has folowing structre. Example:

CREATE TABLE suppliers
  AS (SELECT company_id, address, city, state, zip
      FROM companies
      WHERE company_id < 5000);

In your case you can try something like:

CREATE TABLE retiring_titles
as (
SELECT count(distinct unique_titles.id) from unique_title
)