I tried to automate a SQL query to run for each day of the month, while loop isn't working. Query gives error 1064

user_10377317 0
Viewed 49

I have a query that needs to be run for each day of the month, is there a way I can run a single query so that I don't have to change the date in the query for each time I run it. I have tried using the While loop but it doesn't give me any answers. When I run the code below it shows error 1064

set @StartDate1 = '2020-07-01';
set @EndDate  = '2020-07-30';
WHILE (@StartDate1 < @EndDate) DO
    SELECT COUNT(id ), json_extract(data , '$.metadata.referred')
    FROM modulusDb mdb
    where json_extract(data , '$.event') = 'install' and    json_extract(data , '$.metadata.referred') = TRUE 
    and created_at >= @StartDate1 and created_at < DATEADD(day, 1, @StartDate1)
     SET @StartDate1 = DATEADD(day, 1, @StartDate1)
END WHILE
while-loop mysql sql
1 Answers

You have to this in a procedure or function And every command has to have a semicolon ; at the end

Further DATEADD doesn't exist in mysql or you have a function that has that name

CREATE TABLE modulusDb(id INT, data JSON,created_at DATE);
DROP procedure IF EXISTS `dowhile`;

DELIMITER $$

CREATE PROCEDURE `dowhile`()
BEGIN 
    set @StartDate1 = '2020-07-01';
    set @EndDate  = '2020-07-30';
    WHILE (@StartDate1 < @EndDate) DO
        SELECT COUNT(id ), json_extract(data , '$.metadata.referred')
        FROM modulusDb mdb
        where json_extract(data , '$.event') = 'install' and    json_extract(data , '$.metadata.referred') = TRUE 
        and created_at >= @StartDate1 and created_at < DATE_ADD(@StartDate1, INTERVAL 1 DAY);
         SET @StartDate1 = DATE_ADD(@StartDate1, INTERVAL 1 DAY);
    END WHILE;
END$$

DELIMITER ;

Mysql don't like your select.statement it has no syntax errors but this is incompatible with sql_mode=only_full_group_by

user_51935360
Related