I have following Tables in my database:
Sales
---------------------------------------------
id client account sale Commission
1 X A2 400 100
2 X A2 300 70
3 X A1 1100 100
4 X A1 1200 200
Account Users
-------------------- -------------------------------------
accountid account id username first_name last_name
A1 Acc1 U1 abc Manish Goyal
A2 Acc2 U2 xyz Yogita Sharma
A3 Acc3 U3 jkl Nitish Malhotra
AccountUsers
----------------------------------------
id accountid user Commission(commission_percentage)
1 A1 U1 70
2 A1 U2 20
3 A1 U3 10
I want to get the Sum of sale column and commission column from sales table whose commission is distributed in Associated Account's different users.
I wrote the following SQL
SELECT users.id as user_id, users.first_name, users.last_name,
accounts.account_name as account_name, accounts.accountid as account_id,
SUM(sales.sale) AS sales,
SUM(sales.commission) AS commission,
FROM sales
INNER JOIN accounts ON sales.account = accounts.accountid
INNER JOIN AccountUsers ON AccountUsers.accountid = Accounts.accountid
INNER JOIN Users ON Users.id = AccountUsers.user
GROUP BY Accounts.accountid ,Users.id WITH ROLLUP HAVING (Accounts.accountid IS NOT NULL)
I got the following Result:
user_id first_name last_name account_name sales commission
U1 Manish Goyal ACC1 2300 210
U2 Yogita Sharma ACC1 2300 60
U3 Nitish Malhotra ACC1 2300 30
NULL Nitish Malhotra ACC1 6900 300
However, I want to get following result:
Output
user_id first_name last_name account_name sales commission
U1 Manish Goyal ACC1 2300 210
U2 Yogita Sharma ACC1 2300 60
U3 Nitish Malhotra ACC1 2300 30
NULL Nitish Malhotra ACC1 2300 300
I want the desired output in single query because i also fetch overall grand total. In my database tables there are thousands of records. To get this scenario i have to execute query for twice one for detail output and one for grand total.
Here is a sum up of above example :
For ACC1 (A1) account, in Sales table total sales and commission are 2300 and 300 respectively.
For U1 (Manish Goyal) user, user commission distribution for this user in AccountUsers table is 70 so therefore tatal sale will be 2300 and commission will be (300*70)/100 = 210.
For U2 (Yogita Sharma) user, user commission distribution for this user in AccountUsers table is 20 so therefore tatal sale will be 2300 and commission will be (300*20)/100 = 60.
For U3 (Nitish Malhotra) user, user commission distribution for this user in AccountUsers table is 10 so therefore tatal sale will be 2300 and commission will be (300*10)/100 = 30.
when mysql query run with group by clause with rollup having accountid is not null return the grand total of sales(6900) and commissiom(300).
what i want is grand total for sales should be 2300.
Any help will be appreciated. Thanks in Advance.