sql - The sum of certain records in one table based on records of 3 fields -

have had , maybe asking wrong way can't find related question want.

i have table has 4 fields, id record , cost type, supplement type , amount.

need able add amount field of 2 of cost types 1 of supplement types 1 of id.

pcm_id  cost_type       supplement_type    amount ----------------------------------------------------- 2238    agent           ss                 85.785 2238    dbcost          ss                77.9891 2238    dbcost_tax      ss                 7.7989 2238    dbsell          ss                 85.785 2238    dbsell_tax      ss                 8.5785 2238    pccom_tax       ss                      0 2238    pcmup_tax       ss                      0 2238    retail          ss                 85.785 

so went add dbcost , dbcost_tax ss supplement.

i don't know how query give me value each pcm_id above.

my sql skills limited , self taught here have far.

select sum (pcs.amount)   dbo.pcs    join dbo.pcm on pcm.pcm_id = pcs.pcm_id   pcs.pcm_id = pcm.pcm_id    , pcs.supplement_type = 'ss'    , pcs.cost_type in ('dbcost','dbcost_tax') 

it part of bigger query having trouble part.

hopefully there enough information here can me.

adding 10 aug,

following script need, less few fields straight data.

select     id=pcm.pcm_id,    name=pcm.name,    half_tw_cost=(select round (sum(ppc.pax_tw),2) dbo.ppp         join dbo.ppc on ppp.ppp_id = ppc.ppp_id         pcp.pcp_id = ppp.pcp_id , ppc.cost_type in ('dbcost','dbcost_tax')),    half_tw_sell=(select round (sum(ppc.pax_tw),2) dbo.ppp         join dbo.ppc on ppp.ppp_id = ppc.ppp_id         pcp.pcp_id = ppp.pcp_id , ppc.cost_type in             ('agent','dbsell_tax','pcmup_tax','pccom_tax')),    ss_cost=(select round (sum (pcs.amount),2) dbo.pcs         join dbo.pcm on pcm.pcm_id = pcs.pcm_id         pcs.pcm_id = pcm.pcm_id , pcs.supplement_type = 'ss' , pcs.cost_type         in ('dbcost','dbcost_tax')),    range_seq=pcp.seq,    pax_range=pcp.pxno  dbo.pcm  left join dbo.pcp on pcp.pcm_id = pcm.pcm_id  pcm.pcm_id = 2238 group pcm.pcm_id, pcm.name, pcp.pcp_id, pcp.seq, pcp.pxno  

the results follows:

id     name                   half_tw_cost half_tw_sell  ss_cost      range_seq  pax_range ----------------------------------------------------------------------------------------- 2238   brett test pcm output  106.1200     117.5300      546913.1800  1          10 2238   brett test pcm output  99.7200      110.2400      546913.1800  2          15 2238   brett test pcm output  96.2400      106.2700      546913.1800  3          20 2238   brett test pcm output  94.1500      103.8900      546913.1800  4          25 2238   brett test pcm output  88.5900      102.3000      546913.1800  5          30 2238   brett test pcm output  92.6100      103.2300      546913.1800  6          2 

the value ss_cost total table , not pcm_id. value after 85.79 dbcost + dbcost_tax. know agent value above not including markup on record , change need dbcost , tax. can't calcualte tax services dont have tax not 10%. ss_cost value individual pcm appreciated.

let me explain solution.

grouped_pcm_sums view. sql 2008 supports cte's use here. break problem apart instead of solving in single query. here sums exact data looking , have correct id reference in report query.

if want sums 2238 wasn't sure post, if idea here regardless of pcm_id reporting on, report ss sums 2238... can uncomment commented lines , trade join cross apply shimmy value against rest of report.

grouped_sell_values, grouped_cost_values these views. if noticed correctly, link outter query these sub query on pcp table. instead, should able group pcp_id in these separate operations , join them later.

left joins may not need left joins , left can omitted if want rows relationships exist. since you've performed grouping elsewhere, there no longer need group on other fields them select clause. can add informational fields , subsequent data joins @ will. pay attention row count ensure additional joins don't jostle results unexpectedly.

finally rounding last. check typos , test each cte individually verify produce correct results. taking in smaller chunks, can troubleshoot easier sql instead of 1 large sql.

with grouped_pcm_sums (   select pcm_id -- ss_pcm_id         ,sum(amount) amount     pcs    cost_type in ('dbcost','dbcost_tax')      , supplement_type = 'ss'      --and pcm_id = 2238    group pcm_id ), grouped_sell_values (   select pcp.pcp_id,sum(ppc.pax_tw) half_tw_sell     ppp      join ppc on ppp.ppp_id = ppc.ppp_id     join pcp on ppp.pcp_id = pcp.pcp_id    ppc.cost_type in ('agent','dbsell_tax','pcmup_tax','pccom_tax')    group pcp.pcp_id ), grouped_cost_values (   select pcp.pcp_id,sum(ppc.pax_tw) half_tw_cost     ppp      join ppc on ppp.ppp_id = ppc.ppp_id      join pcp on ppp.pcp_id = pcp.pcp_id    ppc.cost_type in ('dbcost','dbcost_tax')    group pcp.pcp_id ) select pcm.pcm_id       --,ss.ss_pcm_id       ,pcm.name       ,round(cost.half_tw_cost,2) half_tw_cost       ,round(sell.half_tw_sell,2) half_tw_sell       ,round(ss.amount,2) ss_cost       ,pcp.seq       ,pcp.pxno   pcm pcm   -- ss 2238 sums, swap left join following cross apply   -- cross apply grouped_pcm_sums ss   left join grouped_pcm_sums ss on pcm.pcm_id = ss.pcm_id   left join pcp on pcm.pcm_id = pcp.pcm_id   left join grouped_sell_values sell on pcp.pcp_id = sell.pcp_id   left join grouped_cost_values cost on pcp.pcp_id = cost.pcp_id