MySQL query for converting Date (mm-dd-yyyy) to a day of the week of a 3 months data (MySQL Workbench)

Mr. ROBOT 0
Viewed 60

From a supermarket Q1 sales data, I have to extract the highest number of sales by each of the days of the week (Sat,Sun,Mon,etc.). The table is an imported CSV file. It has 'date' column given with "mm-dd-yyyy" format. I also want to know how to import the CSV correctly on MySQL Workbench as I have added the date column as 'datetime' but no idea how to proceed. Here's the DDL I copied from the schema:

CREATE TABLE `supermarket_sales` (   
`Invoice ID` text,   
`Branch` text,   
`City` text,   
`Customer type` text,   
`Gender` text,  
`Product line` text,   
`Unit price` double DEFAULT NULL,   
`Quantity` int DEFAULT NULL,   
`Tax 5%` double DEFAULT NULL,   
`Total` double DEFAULT NULL,   
`Date` datetime DEFAULT NULL,   
`Time` text,  
`Payment` text,   
`cogs` double DEFAULT NULL,   
`gross margin percentage` double DEFAULT NULL,   
`gross income` double DEFAULT NULL,
`Rating` double DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
mysql-workbench mysql
1 Answers

robot i advise you to try this request for change the formats:

DATE_FORMAT(date,format);

I leave you here the documentation: https://www.w3resource.com/mysql/date-and-time-functions/mysql-date_format-function.php

to extract the highest use the function "MAX",

the basic syntaxe:

MAX(DISTINCT expression)

This example uses the MAX() function to return the largest amount of all payments:

SELECT MAX(amount)

FROM payments;

the doc for MAX()

https://www.mysqltutorial.org/mysql-max-function/

For import the CSV file try this:

LOAD DATA INFILE '/home/export_file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

the doc: https://phoenixnap.com/kb/import-csv-file-into-mysql

Or try to change this line : Date datetime DEFAULT NULL,

Aleey-bakugo0
Related