Group BY problem after removing select item 1

[t16turbo]

hi,

I have a select statement which I have tried summing a select item and removing from the GROUP BY.
I get an error saying "not a group by expression" when I try this.

here is my original script:

Select b.project_code,
b.prexternalid,
c.prname,
nbi.baseline_hours/7.5,
nbi.project_name,
cst.dsti_proj_ref, 
cst.dsti_product,
cst.dsti_version,
nbi.customer,
com.company_name,
(b.UNITS/7.5),
sum(a.practsum/27000) as Task_Actuals,
case when obs.level3_name = 'UK' then nbi.budget_hours/7.5
else nbi.budget_hours/8
end As Budget,
case when obs.level3_name = 'UK' then nbi.actual_hours/7.5
else nbi.actual_hours/8
end As Actuals,
case when obs.level3_name = 'UK' then nbi.etc_hours/7.5
else nbi.etc_hours/8
end As ETC,
case when obs.level3_name = 'UK' then (nbi.etc_hours + nbi.actual_hours)/7.5
else (nbi.etc_hours + nbi.actual_hours)/8
end As EAC,
decode((nbi.etc_hours + nbi.actual_hours), 0, 0, nbi.actual_hours/(nbi.etc_hours + nbi.actual_hours)) As pcnt_complete,
decode((nbi.etc_hours + nbi.actual_hours), 0, 0, nbi.actual_hours/(nbi.etc_hours + nbi.actual_hours))*(b.UNITS/7.5) As Total_Effort,
decode((nbi.etc_hours + nbi.actual_hours), 0, 0, nbi.actual_hours/(nbi.etc_hours + nbi.actual_hours))*(b.UNITS/7.5)-sum(a.practsum/27000) AS Net_Effort


from niku.dsti_task_budgets b,
niku.nbi_project_current_facts nbi,
niku.prassignment a,
niku.prtask t,
niku.prchargecode c,
niku.odf_ca_project cst,
niku.nbi_dim_obs obs,
niku.srm_companies com



where 
com.company_id(+) = cst.dsti_true_client3 and
nbi.obs1_unit_id = obs.obs_unit_id
and t.prprojectid = nbi.project_id
and a.prtaskid = t.prid
and t.prchargecodeid = c.prid
and c.prexternalid = b.prexternalid
and b.project_code(+) =nbi.project_code
and cst.id = nbi.project_id
and c.prexternalid IN ('LDEV5320', 'LDEV5340', 'LDEV5330', 'LDEV5150')
     AND nbi.project_name NOT LIKE 'CLOSED%'
     AND nbi.project_name NOT LIKE 'ERROR%'
     AND cst.dsti_version LIKE '15%'
and cst.dsti_proj_ref NOT LIKE 'Error%'
and cst.dsti_proj_ref NOT LIKE 'D00715DAA'
group by
b.project_code,
b.prexternalid,
c.prexternalid,
(b.UNITS/7.5),
case when obs.level3_name = 'UK' then nbi.budget_hours/7.5
else nbi.budget_hours/8
end ,
case when obs.level3_name = 'UK' then nbi.actual_hours/7.5
else actual_hours/8
end ,
case when obs.level3_name = 'UK' then nbi.etc_hours/7.5
else etc_hours/8
end,
case when obs.level3_name = 'UK' then (nbi.etc_hours + nbi.actual_hours)/7.5
else (nbi.etc_hours + nbi.actual_hours)/8
end,
decode((nbi.etc_hours + nbi.actual_hours), 0, 0, nbi.actual_hours/(nbi.etc_hours +  nbi.actual_hours)),
cst.dsti_product,
cst.dsti_version,
c.prname,
nbi.customer,
com.company_name,
cst.dsti_proj_ref,
nbi.project_name,
nbi.baseline_hours

I am trying to use SUM sround the (b.UNITS/7.5)
and then removing (b.UNITS/7.5) from the GROUP BY - so it looks like this:

Select b.project_code,
b.prexternalid,
c.prname,
nbi.baseline_hours/7.5,
nbi.project_name,
cst.dsti_proj_ref, 
cst.dsti_product,
cst.dsti_version,
nbi.customer,
com.company_name,
sum(b.UNITS/7.5),
sum(a.practsum/27000) as Task_Actuals,
case when obs.level3_name = 'UK' then nbi.budget_hours/7.5
else nbi.budget_hours/8
end As Budget,
case when obs.level3_name = 'UK' then nbi.actual_hours/7.5
else nbi.actual_hours/8
end As Actuals,
case when obs.level3_name = 'UK' then nbi.etc_hours/7.5
else nbi.etc_hours/8
end As ETC,
case when obs.level3_name = 'UK' then (nbi.etc_hours + nbi.actual_hours)/7.5
else (nbi.etc_hours + nbi.actual_hours)/8
end As EAC,
decode((nbi.etc_hours + nbi.actual_hours), 0, 0, nbi.actual_hours/(nbi.etc_hours + nbi.actual_hours)) As pcnt_complete,
decode((nbi.etc_hours + nbi.actual_hours), 0, 0, nbi.actual_hours/(nbi.etc_hours + nbi.actual_hours))*(b.UNITS/7.5) As Total_Effort,
decode((nbi.etc_hours + nbi.actual_hours), 0, 0, nbi.actual_hours/(nbi.etc_hours + nbi.actual_hours))*(b.UNITS/7.5)-sum(a.practsum/27000) AS Net_Effort


from niku.dsti_task_budgets b,
niku.nbi_project_current_facts nbi,
niku.prassignment a,
niku.prtask t,
niku.prchargecode c,
niku.odf_ca_project cst,
niku.nbi_dim_obs obs,
niku.srm_companies com



where 
com.company_id(+) = cst.dsti_true_client3 and
nbi.obs1_unit_id = obs.obs_unit_id
and t.prprojectid = nbi.project_id
and a.prtaskid = t.prid
and t.prchargecodeid = c.prid
and c.prexternalid = b.prexternalid
and b.project_code(+) =nbi.project_code
and cst.id = nbi.project_id
and c.prexternalid IN ('LDEV5320', 'LDEV5340', 'LDEV5330', 'LDEV5150')
     AND nbi.project_name NOT LIKE 'CLOSED%'
     AND nbi.project_name NOT LIKE 'ERROR%'
     AND cst.dsti_version LIKE '15%'
and cst.dsti_proj_ref NOT LIKE 'Error%'
and cst.dsti_proj_ref NOT LIKE 'D00715DAA'
group by
b.project_code,
b.prexternalid,
c.prexternalid,
case when obs.level3_name = 'UK' then nbi.budget_hours/7.5
else nbi.budget_hours/8
end ,
case when obs.level3_name = 'UK' then nbi.actual_hours/7.5
else actual_hours/8
end ,
case when obs.level3_name = 'UK' then nbi.etc_hours/7.5
else etc_hours/8
end,
case when obs.level3_name = 'UK' then (nbi.etc_hours + nbi.actual_hours)/7.5
else (nbi.etc_hours + nbi.actual_hours)/8
end,
decode((nbi.etc_hours + nbi.actual_hours), 0, 0, nbi.actual_hours/(nbi.etc_hours +  nbi.actual_hours)),
cst.dsti_product,
cst.dsti_version,
c.prname,
nbi.customer,
com.company_name,
cst.dsti_proj_ref,
nbi.project_name,
nbi.baseline_hours

but as soon as I run that I get the message "not a group by expression"

can anybody suggest why?

 

[SimonSellick]

Yes - you have left the unaggregated column name in the select clause:

nbi.actual_hours))* HERE -->(b.UNITS/7.5)<-- HERE As Total_Effort,
decode((nbi.etc_hours + nbi.actual_hours), 0, 0, nbi.actual_hours/(nbi.etc_hours + nbi.actual_hours))* HERE -->(b.UNITS/7.5)<-- HERE -sum

HTH

 

[t16turbo]

Thanks Simon!

well spotted!