$q = 'SELECT allRows.building_name,  allRows.bldg_dept_grp gb_id,
                SUM(fy_bk_vols) fy_bk_vol,
                SUM(prepaid_bk_vols) prepaid_bk_vol,
                SUM(fy_black_costs) fy_bk_costs,
                SUM(prepaid_bk_costs) prepaid_bk_costs,
                SUM(fy_col_vols) fy_col_vol,
                SUM(prepaid_col_vols) prepaid_col_vol,
                SUM(fy_color_costs) fy_col_costs,
                SUM(prepaid_color_costs) prepaid_col_costs
                

        FROM (SELECT cda.building_name, 
				cd.building_id_ma   bldg_dept_grp,
				SUM(cda.fy_bk_vol) fy_bk_vols,
                0 prepaid_bk_vols,
                SUM(cda.fy_bk_vol * cd.`CostCopy`) fy_black_costs,
                0 prepaid_bk_costs,
                SUM(cda.fy_col_vol) fy_col_vols,
                0 prepaid_col_vols,
                SUM(cda.fy_col_vol * cd.`Mstrcpc`) fy_color_costs,
                0 prepaid_color_costs
               
                FROM current_devices cd
    
                INNER JOIN (SELECT bldg_dept.building_name, 
							SUM(ma.`End` - ma.`Begin`) fy_bk_vol,
                            SUM(ma.`MstrEnd` - ma.`MstrBegin`) fy_col_vol, ma.`SerialNumber`, ma.`EDate` end_date
                        
            		        FROM current_devices ma
            		        INNER JOIN machines mach ON mach.id = ma.model_id
            		        INNER JOIN buildings bldg_dept ON bldg_dept.id = ma.building_id_ma AND bldg_dept.org_id = ma.org_id_ma  
            												   
                            GROUP BY ma.`SerialNumber`, ma.`EDate`) cda ON cda.`SerialNumber` = cd.`SerialNumber`
        			 
        WHERE cd.`SerialNumber` IN(SELECT `SerialNumber` FROM current_devices cdv WHERE org_id_ma = 62)
        AND cd.`SerialNumber` NOT IN(SELECT bida.serial_number                      
                  			FROM billing_data_archive bida 
                  			LEFT JOIN organization vendOrg ON vendOrg.id = bida.vendor_id
                  			WHERE bida.org_id_ma = 62 AND bida.billing_cycle != 10	        
                  			AND bida.school_year = 23  AND bida.billing_type = "Client"   
                   			GROUP BY bida.serial_number)                
            
        GROUP BY cda.building_name,  cd.building_id_ma  

UNION ALL


SELECT cda.building_name,  bda.building_id_ma 
    	gb_id, SUM(cda.fy_bk_vol) fy_bk_vols,
        SUM(bda.prepaid_blk_vol) prepaid_bk_vols,
        SUM(cda.fy_bk_vol * bda.cpc_black) fy_black_costs,
        SUM(bda.prepaid_blk_costs) prepaid_bk_costs,
    	SUM(cda.fy_col_vol) fy_col_vols,
    	SUM(bda.prepaid_col_vol) prepaid_col_vols,
        SUM(cda.fy_col_vol * bda.cpc_color) fy_color_costs,
        SUM(bda.prepaid_col_costs) prepaid_color_costs
    
FROM (SELECT MAX(bida.billing_cycle), bida.serial_number, bida.vendor_id,
          vendOrg.org_name, bida.cpc_color, bida.cpc_black,
          bida.vendor_cpc_black, bida.vendor_cpc_color,
          bida.billing_type, bida.billing_cycle, bida.school_year, bida.org_id_ma,                     
          Sum(bida.billed_projected_volume_color) prepaid_col_vol,
          Sum(bida.billed_projected_volume_color * bida.cpc_color) prepaid_col_costs,
          Sum(bida.billed_projected_volume_black) prepaid_blk_vol,
          Sum(bida.billed_projected_volume_black * bida.cpc_black) prepaid_blk_costs,
          bida.building_id_ma 
          
                      
		FROM billing_data_archive bida 
    LEFT JOIN organization vendOrg ON vendOrg.id = bida.vendor_id
		WHERE bida.org_id_ma = 62 AND bida.billing_cycle != 10	        
		AND bida.school_year = 23  AND bida.org_id_ma is not null AND bida.billing_type = "Client"  
    GROUP BY bida.serial_number) bda
            
INNER JOIN (SELECT bldg_dept.building_name,  bldg_dept.id bldg_dept_id, SUM(cd.`End` - cd.`Begin`) fy_bk_vol,
                SUM(cd.`MstrEnd` - cd.`MstrBegin`) fy_col_vol, cd.`SerialNumber`,
                cd.`EDate` end_date
                
    			FROM current_devices cd
    			INNER JOIN machines mach ON mach.id = cd.model_id
    			INNER JOIN buildings bldg_dept ON bldg_dept.id = cd.building_id_ma AND bldg_dept.org_id = cd.org_id_ma										   
                GROUP BY cd.`SerialNumber`, cd.`EDate`) cda ON cda.`SerialNumber` = bda.serial_number 
				AND cda.bldg_dept_id = bda.building_id_ma 
    
    GROUP BY bda.building_id_ma ) allRows    
    GROUP BY allRows.bldg_dept_grp;';