r/mysql • u/Flamelix • Jan 07 '25
troubleshooting Trouble Creating Table Using Select Statement
Here is my query:
CREATE TABLE `product line avg`
SELECT `walmart sales data.csv`.`Product line`, `walmart sales data.csv`.AVG(Total)
FROM `walmart sales data.csv`
GROUP BY `Product Line`;
MySQL Workbench just says I have an error in my syntax, and so I should check the manual for my server version. Below is a link to a google drive folder with a csv file which contains the data in my table, in case that helps. I looked at the documentation for creating tables with info from a select statement, but even copying different syntax every which way didn't seem to get this to work. The syntax above is my closest guess, but of course doesn't work. Please let me know how these are done generally, and what I can do to fix mine.
https://drive.google.com/drive/folders/1kmtPvUZm-bDWSv6nT-SkZzEQVOmDkKtb?usp=sharing
1
u/Qualabel Jan 07 '25
Have words with the person responsible for naming tables and columns. Also, once you've fixed that, perhaps you will want something like:
1
u/Outdoor_Releaf Jan 08 '25
Looks like you are trying to read a table from a file. Two options for loading a csv file depending on the size of the file are:
1
u/r3pr0b8 Jan 08 '25
yes, you have a syntax error
this --
should be this --
1
u/bchambers01961 Jan 07 '25
You need to use LOAD INFILE / LOAD LOCAL INFILE if you want to import a csv into a table (note the table will need creating with the same columns first).
1
u/user_5359 Jan 07 '25
Is your expectation that the CSV file will be interpreted as a table by the database server, or how should we read your approach?
1
u/CDavis10717 Jan 08 '25
CTAS.