select t1.name1, t1.salesman, t1.total_price, t2.total_price1 from (
(select res_users.id, 'กระจกเทมเปอร์' as name1,
res_users.name as salesman,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date between '2012-05-01' and '2012-05-07'
and sale_order.state = 'done'
and product_product.default_code like 'st%'
and not (product_product.default_code like 'stc%' or default_code like 'stb%')
and not res_partner.id = '874'
group by res_users.id,salesman)
union
(select res_users.id, 'กระจกลามิเนท' as name1,
res_users.name as salesman,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date between '2012-05-01' and '2012-05-07'
and sale_order.state = 'done'
and product_product.default_code like 'sl%'
group by res_users.id,salesman)
union
(select res_users.id, 'กระจกธรรมดา' as name1,
res_users.name as salesman,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date between '2012-05-01' and '2012-05-07'
and sale_order.state = 'done'
and product_product.default_code like 'sa%'
and not (product_product.default_code like 'sac%' or default_code like 'sab%')
group by res_users.id,salesman)
union
(select res_users.id, 'กระจกพ่นสี-พ่นทราย' as name1,
res_users.name as salesman,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date between '2012-05-01' and '2012-05-07'
and sale_order.state = 'done'
and (product_product.default_code like 'sac%' or default_code like 'stc%' or default_code like 'sab%' or default_code like 'stb%')
group by res_users.id,salesman)) t1
left join
((select res_users.id, 'กระจกเทมเปอร์' as name1,
res_users.name as salesman1,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price1
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date = '2012-05-07'
and sale_order.state = 'done'
and product_product.default_code like 'st%'
and not (product_product.default_code like 'stc%' or default_code like 'stb%')
and not res_partner.id = '874'
group by res_users.id,salesman1)
union
(select res_users.id, 'กระจกลามิเนท' as name1,
res_users.name as salesman1,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price1
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date = '2012-05-07'
and sale_order.state = 'done'
and product_product.default_code like 'sl%'
group by res_users.id,salesman1)
union
(select res_users.id, 'กระจกธรรมดา' as name1,
res_users.name as salesman1,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price1
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date = '2012-05-07'
and sale_order.state = 'done'
and product_product.default_code like 'sa%'
and not (product_product.default_code like 'sac%' or default_code like 'sab%')
group by res_users.id,salesman1)
union
(select res_users.id, 'กระจกพ่นสี-พ่นทราย' as name1,
res_users.name as salesman1,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price1
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date = '2012-05-07'
and sale_order.state = 'done'
and (product_product.default_code like 'sac%' or default_code like 'stc%' or default_code like 'sab%' or default_code like 'stb%')
group by res_users.id,salesman1)) t2 on t1 = t2
order by t1.name1, t1.salesman, t2.salesman1, t1.total_price, t2.total_price1
select t1.salesman, t1.total_price, t2.total_price1 from (
(select res_users.id,
res_users.name as salesman,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price
from sale_order
left join res_company on res_company.id = sale_order.company_id
left join res_partner on res_partner.id = sale_order.partner_id
left join res_partner_address on res_partner_address.id = sale_order.partner_invoice_id
left join res_partner_address as cusship on cusship.id = sale_order.partner_shipping_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_uom on product_uom.id = sale_order_line.product_uom
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
left join product_template on product_product.product_tmpl_id = product_template.id
left join product_category on product_template.categ_id = product_category.id
left join product_thick on product_product.thick = product_thick.id
where sale_order.requested_date between '2012-05-01' and '2012-05-07'
and sale_order.state = 'done'
and (product_product.default_code like 'st%'
or default_code like 'sl%'
or default_code like 'sa%'
or default_code like 'sac%'
or default_code like 'stc%'
or default_code like 'sab%'
or default_code like 'stb%')
group by res_users.id, res_users.name)) t1
left join
((select res_users.id,
res_users.name as salesman1,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price1
from sale_order
left join res_company on res_company.id = sale_order.company_id
left join res_partner on res_partner.id = sale_order.partner_id
left join res_partner_address on res_partner_address.id = sale_order.partner_invoice_id
left join res_partner_address as cusship on cusship.id = sale_order.partner_shipping_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_uom on product_uom.id = sale_order_line.product_uom
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
left join product_template on product_product.product_tmpl_id = product_template.id
left join product_category on product_template.categ_id = product_category.id
left join product_thick on product_product.thick = product_thick.id
where sale_order.requested_date = '2012-05-07'
and sale_order.state = 'done'
and (product_product.default_code like 'st%'
or default_code like 'sl%'
or default_code like 'sa%'
or default_code like 'sac%'
or default_code like 'stc%'
or default_code like 'sab%'
or default_code like 'stb%')
group by res_users.id, salesman1)) t2 on t1.id = t2.id
Load balance : Server 03
