BMW 11May 1-4.sql

SELECT
		 count(*) over(PARTITION BY d."Agent_Name"  ) as count_of_rows,
		 IFNULL(d."Override_Agent_Commission", 0) as Override_Agent_Commission_Final,
		 IFNULL(d.Agent_Commission, 0) as Agent_Commission_Final,
		 d.*
FROM (	SELECT
			 row_number() over() as row_number,
			 a."Name" as Agent_Name,
			 a."ID",
			 a."Override Rate" as Sub_Subrate,
			 a."Commission Rate" as Agent_Com_Rate,
			 round(c."Total Commission" * a."Commission Rate" / 100, 2) as Agent_Commission,
			 null as Override_Agent_Commission,
			 c.*
	FROM  "TelarusSync" c
LEFT JOIN "BMW Agent Com Rates" a ON lower(a."ID")  = lower(c."Partner Order ID")  
	UNION
 	SELECT
			 row_number() over() as row_number,
			 a."Override Partner" as Agent_Name,
			 a."ID",
			 a."Override Rate" as Sub_Subrate,
			 a."Commission Rate" as Agent_Com_Rate,
			 null as Agent_Commission,
			 round(c."Total Commission" * a."Override Rate" / 100, 2) as Override_Agent_Commission,
			 c.*
	FROM  "TelarusSync" c
LEFT JOIN "BMW Agent Com Rates" a ON lower(a."ID")  = lower(c."Partner Order ID")  
 
) d 
ORDER BY d."Agent_Name" ASC