How many days a week (Monday, Tuesday...) do you have in a 1-month period?

Question

I need help to return in an SQL the amount you have of each day of the week in a period of 1 month, for example in the month 06/2021 has 4 Mondays,5 Tuesdays,5 Wednesdays...etc. I need to know the amount of every day of the week, because I need to average that amount. I tried several ways but it didn’t work, I need it to be in SQL Firebird. Thank you. Below this one of the forms I tried, it returns to every day of the week the amount of 560:

SELECT sum(iif(EXTRACT(WEEKDAY FROM N.DTEMISSAO) = 1, 1, 0)) qt_segunda,
        sum(iif(EXTRACT(WEEKDAY FROM N.DTEMISSAO) = 1, 1, 0)) qt_terca,
        sum(iif(EXTRACT(WEEKDAY FROM N.DTEMISSAO) = 1, 1, 0)) qt_quarta,
        sum(iif(EXTRACT(WEEKDAY FROM N.DTEMISSAO) = 1, 1, 0)) qt_quinta,
        sum(iif(EXTRACT(WEEKDAY FROM N.DTEMISSAO) = 1, 1, 0)) qt_sexta,
        sum(iif(EXTRACT(WEEKDAY FROM N.DTEMISSAO) = 1, 1, 0)) qt_sabado,
        sum(iif(EXTRACT(WEEKDAY FROM N.DTEMISSAO) = 1, 1, 0)) qt_domingo
FROM NOTA N
WHERE N.DTEMISSAO BETWEEN '01.06.2021' AND '30.06.2021'

Answer 1

That’s not very simple because of some things:

• It is necessary to group by dates, but there may be more than one record on the same date, so it is necessary to make a DISTINCT;
• To make a DISTINCT, it is necessary to do in a subquery, ie first return all unique dates;
• That one SUM doesn’t make sense, could be a COUNT, anyway it is necessary to separate the days, then a better opição would be to use a CASE WHEN.

That said, let’s go to the query:

select case 
         when extract(dow from dados.data)=0 THEN 'DOM'
         when extract(dow from dados.data)=1 THEN 'SEG'
         when extract(dow from dados.data)=2 THEN 'TER'
         when extract(dow from dados.data)=3 THEN 'QUA'
         when extract(dow from dados.data)=4 THEN 'QUI'
         when extract(dow from dados.data)=5 THEN 'SEX'
         when extract(dow from dados.data)=6 THEN 'SAB'
       end dia,
       count(extract(dow from dados.data)) tot
 from (select distinct dtemissao as data
        from nota) dados
group by extract(dow from dados.data)
order by extract(dow from dados.data);

Remarks:

• To subquery select distinct dtemissao as data, called "date", will return the unique dates. Note that if your dates have time will need to remove the date to group correctly;
• To identify the day of the week was used extract(dow from dados.data)=0, to learn more, see the documentation: https://www.postgresql.org/docs/8.1/functions-datetime.html
• In the end, grouped by the same criterion extract(dow from dados.data) who made the COUNT.

You can see it working here: http://sqlfiddle.com/, insert some date for example