SQL query - Count with date

Good afternoon,

You could use a function to calculate the number of days and from there use flow control structures to see, depending on the day of the week you want to know (from 1 to 7 in MySQL starting on Sunday) and two dates concrete:

    CREATE FUNCTION howmanydays(dayselected integer, startdate date, enddate date) RETURNS integer
        DETERMINISTIC
    BEGIN
            DECLARE startdayofweek integer;
            DECLARE enddayofweek integer;
            DECLARE numberdays integer;
            DECLARE numberweeks integer;
            DECLARE restdays integer;
            DECLARE numberdaysofweek integer;
            IF dayselected BETWEEN 1 AND 7 THEN
                    SET startdayofweek = DAYOFWEEK(startdate);
                    SET enddayofweek = DAYOFWEEK(enddate);
                    SET numberdays = FLOOR(DATEDIFF(enddate,startdate));
                    SET numberweeks = FLOOR(numberdays/7);
                    SET restdays = numberdays%7;
                    SET numberdaysofweek = numberweeks;
                    IF enddayofweek > startdayofweek THEN
                            IF dayselected BETWEEN startdayofweek AND enddayofweek THEN
                            SET numberdaysofweek = numberdaysofweek + 1;
                            END IF;
                    ELSE
                            IF dayselected BETWEEN startdayofweek and 7 OR dayselected BETWEEN 1 and enddayofweek THEN
                            SET numberdaysofweek = numberdaysofweek + 1;
                            END IF; 
                    END IF;
            ELSE
                    SET numberdaysofweek = -1;
            END IF;
            RETURN numberdaysofweek;
    END;//

I have used these statements to prove it:

    SELECT howmanydays(1,'2017-03-09','2017-03-25');//
    SELECT howmanydays(6,'2017-03-09','2017-03-25');//
    SELECT howmanydays(2,'2017-03-09','2017-03-27');//
    SELECT howmanydays(4,'2017-03-09','2017-03-27');//
    SELECT howmanydays(7,'2016-12-29','2016-12-31');//
    SELECT howmanydays(8,'2011-12-31','2011-12-29');//

See if this helps you: use DATENAME or DATEPART :

SELECT DATENAME(dw,GETDATE()) -- Viernes
SELECT DATEPART(dw,GETDATE()) -- 6

Then you can do the COUNT() .