I have 2 tables Games and Trading I use this formula sum(EntryFee * Rake/(100 Rake)*TotalEntry) in Games table to get the value
I am using this query in transaction table count(different user id) to get the value
Now I want to divide the value of [sum(EntryFee * Rake/(100 Rake)*TotalEntry)] by the value of [count(distinct UserID)]
For example sum(EntryFee * Rake/(100 Rake)*TotalEntry) = 90 and count(distinct UserID) = 3 Then 90/3 =30 How can I do this in MYSQL
CREATE TABLE Games (EntryFee INT, Rake INT, TotalEntry INT); CREATE TABLE Transaction1 (UserID VARCHAR(25)); INSERT INTO Games VALUES (30,16,150),(45,20,100),(15,5,50),(25,20,300),(10,8,270); INSERT INTO Transaction1 VALUES ('Daniel'),('David'),('John'),('Martha'); SELECT Games.EntryFee, Games.Rake, Games.TotalEntry, COUNT(distinct Transaction1.UserID) AS CountUser, (Games.EntryFee * Games.Rake / (100 + Games.Rake) * Games.TotalEntry / COUNT(distinct Transaction1.UserID)) AS Calculate FROM Games JOIN Transaction1 GROUP BY Games.EntryFee, Games.Rake, Games.TotalEntry;result:
Example query
SELECT ( SELECT sum(EntryFee * Rake/(100 + Rake)*TotalEntry) FROM Games )/( SELECT count(distinct UserID) FROM Transaction ) MyResult