如何从不同的表中获取数据量？（2个条件的内连接）

有表：

users

+------------+-------------------+------+-----+---------+----------------+
| Field      | Type              | Null | Key | Default | Extra          |
+------------+-------------------+------+-----+---------+----------------+
| id         | int unsigned      | NO   | PRI | NULL    | auto_increment |
| date       | datetime          | NO   |     | NULL    |                |
| refererId  | int               | YES  |     | NULL    |                |
+------------+-------------------+------+-----+---------+----------------+

和表actions

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int unsigned     | NO   | PRI | NULL    | auto_increment |
| userId     | int unsigned     | NO   |     | NULL    |                |
| refererId  | int unsigned     | YES  |     | NULL    |                |
| added      | datetime         | NO   |     | NULL    |                |
| amount     | int unsigned     | NO   |     | 0       |                |
| commission | int unsigned     | NO   |     | 0       |                |
| onHold     | tinyint unsigned | NO   |     | 1       |                |
+------------+------------------+------+-----+---------+----------------+

如何获取按日期分组的注册和操作数量？在表中为每个操作actions创建一行。我添加了两个内部函数并尝试将表名放在计数的 ifs 中，但仍然不是我需要的。我试图这样做，但它只返回当天的活动：

SELECT COUNT(if(amount > 0,1,NULL)) 'actions', COUNT(if(DATE(added) = DATE(date) AND amount != 0,1,NULL)) 'registration',  DATE(added) AS added, SUM(amount) AS amount, SUM(commission) AS comission
FROM actions
INNER JOIN user ref ON ref.id = actions.refererId
#INNER JOIN user use ON use.id = actions.userId
where actions.refererId = 1 AND onHold = 1
group By DATE(actions.added)

结果：

+-------+--------------+------------+--------+-----------+
| actions | registration | added      | amount | comission |
+---------+--------------+------------+--------+-----------+
|     3   |            0 | 2022-05-16 |    210 |        63 |
|     1   |            0 | 2022-04-28 |     15 |         2 |
+---------+--------------+------------+--------+-----------+

我试图这样做，但它只返回当天的注册：

SELECT COUNT(if(amount > 0,1,NULL)) 'actions', COUNT(if(DATE(added) = DATE(date) AND amount != 0,1,NULL)) 'registration',  DATE(added) AS added, SUM(amount) AS amount, SUM(commission) AS comission
FROM actions
#INNER JOIN user ref ON ref.id = actions.refererId
INNER JOIN user use ON use.id = actions.userId
where actions.refererId = 1 AND onHold = 1
group By DATE(actions.added)

结果：

+---------+--------------+------------+--------+-----------+
| actions | registration | added      | amount | comission |
+---------+--------------+------------+--------+-----------+
|      1  |            1 | 2022-04-28 |     15 |         2 |
+---------+--------------+------------+--------+-----------+

预期结果：

+---------+--------------+------------+--------+-----------+
| actions | registration | added      | amount | comission |
+---------+--------------+------------+--------+-----------+
|       3 |            0 | 2022-05-16 |    210 |        63 |
|       1 |            1 | 2022-04-28 |     15 |         2 |
+---------+--------------+------------+--------+-----------+