I have tried many queries with no right result
my goal is to get user commission based on userid attend to branchid and how many users was attended on same day on same branch;
want have result like so
| Date | totalAtt | Amount |
|---|---|---|
| 2022-05-1 | 1 | 60 <-(50+20)-(5+5) |
| 2022-05-2 | 2 | 50 <-(50+20)-(5+15) |
| 2022-05-3 | 3 | 80 <-(80+30)-(25+5) |
| 2022-05-4 | 1 | 200 <-(100+200)-(50+50) was on branchid =5 |
from the 2 tables below money
| ID | Date | branchId | ca | ce | car | cer |
|---|---|---|---|---|---|---|
| 1 | 2022-05-1 | 7 | 50 | 20 | 5 | 5 |
| 2 | 2022-05-1 | 5 | 100 | 20 | 10 | 5 |
| 3 | 2022-05-2 | 7 | 50 | 20 | 5 | 15 |
| 4 | 2022-05-2 | 5 | 70 | 20 | 10 | 5 |
| 5 | 2022-05-3 | 7 | 80 | 30 | 25 | 5 |
| 6 | 2022-05-3 | 5 | 90 | 20 | 35 | 5 |
| 7 | 2022-05-4 | 7 | 80 | 30 | 25 | 5 |
| 8 | 2022-05-4 | 5 | 100 | 200 | 50 | 50 |
att
| ID | date | userid | branchId | att |
|---|---|---|---|---|
| 1 | 2022-05-1 | 20 | 7 | 1 |
| 2 | 2022-05-2 | 20 | 7 | 1 |
| 3 | 2022-05-2 | 21 | 7 | 1 |
| 4 | 2022-05-3 | 20 | 7 | 1 |
| 5 | 2022-05-3 | 21 | 7 | 1 |
| 6 | 2022-05-3 | 22 | 7 | 1 |
| 7 | 2022-05-4 | 20 | 5 | 1 |
thanks in Advanced
It seems you want to count att by date but amount for a specified user. So conditionally aggregate
SELECT
a.date,
COUNT(DISTINCT a.USERID) AS totalatt ,
SUM(CASE WHEN USERID = 20 THEN (m.ca+m.ce)-(m.car+m.cer) END) AS amount
FROM att a
LEFT JOIN money m ON a.date = m.date AND a.branchid = m.branchid
GROUP BY a.date HAVING AMOUNT > 0
ORDER BY a.date;