Select a1.*,count(a6.indent_no) as tot,round(sum(a6.reqd_qty*a6.rate)+sum((a6.reqd_qty*a6.rate)*a6.tax_per/100),0) as indt_value,
concat(a2.f_name,' ',a2.l_name) As indentor_name, a5.department 
From materials_management.indent_mast a1 
join hrlms.employee_mast a2 on a1.indentor=a2.emp_id 
join materials_management.dept_mast a5 on a1.department_id=a5.Id 
join materials_management.indent_tran a6 on a1.indent_no=a6.indent_no And a6.active='Y' 
group by a1.indent_no  
Order by indent_no;
------------------------------------------------------------------------------------------------------------------
Select a1.indent_no,a1.indent_date,if(a1.type_of_indent='C','Capex','Revenue') As Indt_Type,concat(a2.f_name,' ',a2.l_name) As indentor_name, a5.department,a1.indent_particulars,
count(a6.indent_no) as indt_items,round(sum(a6.reqd_qty*a6.rate)+sum((a6.reqd_qty*a6.rate)*a6.tax_per/100),0) as indt_value
From materials_management.indent_mast a1 
join hrlms.employee_mast a2 on a1.indentor=a2.emp_id 
join materials_management.dept_mast a5 on a1.department_id=a5.Id 
join materials_management.indent_tran a6 on a1.indent_no=a6.indent_no And a6.active='Y' 
group by a1.indent_no  
Order by indent_no;