PHP/SQL - How to count saturdays in each month?-CodePudding

I have two MySQL tables, called "accounts" and "events".

Accounts

ID	name
1	Pete
2	Josh
3	Harry

Events

ID	date	account_id
1	2021-10-09	1
2	2021-09-25	1
3	2021-10-23	2
4	2021-11-06	1
5	2021-10-13	1
6	2021-11-17	2
7	2021-11-06	3
8	2021-12-04	3

The account_id in the events table is linked to the id in the accounts table.

My question is: which query can I use to count saturdays in each month (date YYYY-mm-dd format) for each unique user in the accounts table? So I get the next result:

Name	September	October	November	December
Josh	0	1	0	0
Pete	1	1	1	0
Harry	0	0	1	1

I've tried many queries (with i.e. the (inner) JOIN, DISTINCT and GROUP BY keywords) but I don't get the exact result. Can you please help me?

Many thanks in advance!

CodePudding user response：

Use WEEKDAY to tell if a date is Saturday

Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

SELECT
 MAX(a.name) name
 , (
   SUM( CASE WHEN e.`date` between '2021-09-01' AND '2021-09-30' THEN 1 ELSE 0 END)
 ) "September"
 , (
   SUM( CASE WHEN e.`date` between '2021-10-01' AND '2021-10-31' THEN 1 ELSE 0 END)
 ) "October"
 , (
   SUM( CASE WHEN e.`date` between '2021-11-01' AND '2021-11-30' THEN 1 ELSE 0 END)
 ) "November"
 , (
   SUM( CASE WHEN e.`date` between '2021-12-01' AND '2021-12-31' THEN 1 ELSE 0 END)
 ) "December"
FROM Accounts a
LEFT JOIN Events e ON e.account_id = a.id AND WEEKDAY(e.`date`) = 5
GROUP BY a.id
;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2689e975b18f3a208fdda2d78b50b56c

CodePudding user response：

Basically you can use DAYOFWEEK function and GROUP BY MONTH

SELECT 
    account_id,
    MONTH(date), 
    COUNT(*)
FROM Events 
WHERE DAYOFWEEK(date) = 7
GROUP BY account_id, MONTH(date);

SQL Fiddle here

and when you can use PIVOT on received table like:

WITH res AS (
  SELECT 
      account_id,
      MONTH(date) mnth, 
      COUNT(*) cnt
  FROM Events 
  WHERE DAYOFWEEK(date) = 7
  GROUP BY account_id, MONTH(date)
) SELECT 
    account_id,
    name,
    SUM(mnth=1) Januar,
    -- 
    SUM(mnth=9) September,
    SUM(mnth=10) October,
    SUM(mnth=11) November,
    SUM(mnth=12) December
FROM res
JOIN Accounts ON Accounts.id = account_id
GROUP BY account_id, name;

SQL Pivot Fiddle