mysql COUNT and Sum with join 2 tables-CodePudding

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

CodePudding user response：

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;