CODEWITHSUNDEEP
mysqljoincountsum
David Isaac
David Isaac

Reputation: 3

mysql COUNT and Sum with join 2 tables

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

Upvotes: -2

Views: 56

Answers (1)

P.Salmon
P.Salmon

Reputation: 17655

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;

Upvotes: 0

Related Questions