Example : Sales to Agent LINKING TABLES


plus2_agent

agent_id
Autoincrement ID field, Unique id of the user / agent
coms
float with 2 decimal places, Commission of the user / agent
name
varchar(25) Name of the user / agent
email
varchar(30) Email address of the user / agent
address
varchar(30) Address of the user / agent

plus2_sales

sales_id
Autoincrement ID field, Unique id of the sales
agent_id
integer , id of the user / agent for the sale
dt_sale
Date field, Date of the sale ( can take multiple values of same date )
p_id
integer , id of the product ( can be linked to product table , future )
quantity
integer, number of product sold in the sale
total
Integer , based on this value commission will be calculated for the user / agent
Download the SQL dump of tables with sample rows to execute these queries at the end of this page.

All sales in the order of Date ( starting from recent date )
SELECT * FROM plus2_sales ORDER BY dt_sales DESC
Some records are shown here ( not all )
sales_idagent_iddt_salesp_idquantitytotal
122021-06-0782492
212021-06-0774408
342021-06-0755492
412021-06-0714598
512021-06-0652406
Total sales date wise using GROUP BY and in the ORDER of most recent date.
SELECT dt_sales,SUM(total) FROM `plus2_sales` 
 GROUP BY dt_sales ORDER BY dt_sales DESC
Last 5 days sales in the order of Date, we used LIMIT here.
SELECT dt_sales,SUM(total) FROM plus2_sales GROUP BY dt_sales
   ORDER BY dt_sales DESC LIMIT 0,5
dt_salesSUM(total)
2021-06-071990
2021-06-062116
2021-06-052018
2021-06-041986
2021-06-031790
Daily sales based on agent_id, we used SUM() here.
SELECT dt_sales, agent_id,SUM(total) FROM `plus2_sales`
 GROUP BY dt_sales,agent_id   order by dt_sales DESC 
Daily sales based on agent_id and display name of the agent.
SELECT dt_sales, a.agent_id, b.name,SUM(total) FROM plus2_sales a 
LEFT JOIN plus2_agent b on a.agent_id=b.agent_id  
GROUP BY dt_sales,agent_id   order by dt_sales DESC
Few records ( not all rows ) are shown here
dt_salesagent_idnameSUM(total)
2021-06-071Alex1006
2021-06-072Ronn492
2021-06-074King492
2021-06-061Alex406
2021-06-062Ronn590
2021-06-064King1120
2021-06-052Ronn581
2021-06-054King1437
2021-06-042Ronn523
2021-06-043Ravi480
2021-06-044King983
Daily sales and daily commission against each agent with name.
SELECT dt_sales, a.agent_id, b.name,SUM(total),
FORMAT(b.coms*SUM(total),2) as commission  
FROM plus2_sales a LEFT JOIN plus2_agent b ON a.agent_id=b.agent_id 
 GROUP BY dt_sales,agent_id   order by dt_sales DESC
Few records ( not all rows )
dt_salesagent_idnameSUM(total)commission
2021-06-071Alex1006120.72
2021-06-072Ronn49254.12
2021-06-074King492113.16
2021-06-061Alex40648.72
2021-06-062Ronn59064.90
2021-06-064King1120257.60
2021-06-052Ronn58163.91
2021-06-054King1437330.51
2021-06-042Ronn52357.53
2021-06-043Ravi48057.60
2021-06-044King983226.09
2021-06-031Alex1790214.80
2021-06-023Ravi1707204.84
2021-06-024King481110.63
Total commission against all the agents ( without any date restrictions )
SELECT  a.agent_id, b.name, b.coms,SUM(total),
 FORMAT(b.coms*SUM(total),2) as commission  
 FROM plus2_sales a LEFT JOIN plus2_agent b on a.agent_id=b.agent_id
 GROUP BY agent_id
agent_idnamecomsSUM(total)commission
1Alex0.126206744.72
2Ronn0.113796417.56
3Ravi0.124112493.44
4King0.2359691,372.87
Total Commission against all the agents in last 4 days ( use a fresh copy of sample sql dump below ) , more about CURDATE() and Date calculations here.
SELECT  a.agent_id, b.name, b.coms,SUM(total),
 FORMAT(b.coms*SUM(total),2) as commission  
 FROM plus2_sales a LEFT JOIN plus2_agent b on a.agent_id=b.agent_id
WHERE   dt_sales >= DATE_SUB(CURDATE(), INTERVAL 4 DAY)
GROUP BY agent_id 
agent_idnamecomsSUM(total)commission
1Alex0.121412169.44
2Ronn0.112186240.46
3Ravi0.1248057.60
4King0.234032927.36
Total Commission against all the agents in current month ( use a fresh copy of sample sql dump below )
SELECT  a.agent_id, b.name, b.coms,SUM(total),
 FORMAT(b.coms*SUM(total),2) as commission  
 FROM plus2_sales a LEFT JOIN plus2_agent b on a.agent_id=b.agent_id
WHERE dt_sales BETWEEN DATE_FORMAT(CURDATE(),'%Y-%m-01') AND CURDATE()
GROUP BY agent_id
agent_idnamecomsSUM(total)commission
1Alex0.124131495.72
2Ronn0.112778305.58
3Ravi0.122187262.44
4King0.2350051,151.15
Above query for current year
SELECT  a.agent_id, b.name, b.coms,SUM(total),
 FORMAT(b.coms*SUM(total),2) as commission  
 FROM plus2_sales a LEFT JOIN plus2_agent b on a.agent_id=b.agent_id
WHERE dt_sales BETWEEN DATE_FORMAT(CURDATE(),'%Y-01-01') AND CURDATE()
GROUP BY agent_id 
agent_idnamecomsSUM(total)commission
1Alex0.126206744.72
2Ronn0.113796417.56
3Ravi0.124112493.44
4King0.2359691,372.87
We will be changing the WHERE condition part to get records in different ranges.
Present calendar week data using the above query we will get.
SELECT  a.agent_id, b.name, b.coms,SUM(total),
 FORMAT(b.coms*SUM(total),2) as commission  
 FROM plus2_sales a LEFT JOIN plus2_agent b on a.agent_id=b.agent_id
WHERE   WEEKOFYEAR(dt_sales)=WEEKOFYEAR(CURDATE())
GROUP BY agent_id
agent_idnamecomsSUM(total)commission
1Alex0.121006120.72
2Ronn0.1149254.12
4King0.23492113.16
The following SQL dump is created dynamically by using todays date and random numbers. While using queries having CURDATE() will return different results based on the date of run of the query. So always use a fresh copy of this SQL dump to check all these queries.


LEFT JOIN ( Basic Query) RIGHT JOIN INNER Join CROSS Join
SQL Union
LEFT JOIN using Multiple Tables Exercise on LEFT JOIN using Product & Sales Tables LINKING TABLES

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com






    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer