|
SELECT a.business_unit ,a.bidder_id ,COUNT(a.auc_id) , ( SELECT COUNT(b.auc_id) FROM ps_auc_par_bi_vw b WHERE a.business_unit = b.business_unit AND a.bidder_id = b.bidder_id GROUP BY b.business_unit,b.bidder_id ), ( SELECT SUM(c.auc_ext_price) FROM ps_auc_par_bi_vw c WHERE a.business_unit = c.business_unit AND a.bidder_id = c.bidder_id AND c.auc_version=( SELECT MAX(e.auc_version) FROM PS_AUC_HDR e WHERE e.auc_id=c.auc_id AND e.auc_round=c.auc_round AND e.business_unit=c.business_unit ) AND c.auc_round=( SELECT MAX(d.auc_round) FROM PS_AUC_HDR d WHERE d.auc_id=c.auc_id AND d.business_unit=c.business_unit ) GROUP BY c.business_unit,c.bidder_id ),%DecMult(%DecDiv(( SELECT COUNT(b.auc_id) FROM ps_auc_par_bi_vw b WHERE a.business_unit = b.business_unit AND a.bidder_id = b.bidder_id GROUP BY b.business_unit,b.bidder_id ),COUNT(a.auc_id)),100),'USD' FROM ps_auc_in_bi_vw a GROUP BY a.business_unit,a.bidder_id
|