sums and groups

[dcm]

Hello,
Below is my current query, I was wondering if anyone could tell me how to sum
the opt_est_contract_val column correctly. The problem is with that query it
repeats that value due to other data points that have to be joined in. I do
have another column called opt_id that would be the logical column to group by
but can't seem to get syntax right. If I group the whole query by opt_id, it
will throw off my other sum. This is an Oracle DB 8.x or 9.x I believe.
Thanks for any help.

select count(*) as total, concat(concat(t1.user_last_name,',
'),t1.user_first_name) as mgr, sum(bt_opportunity.opt_est_contract_val) as
val_total, sum(dev_qty) as dev_total
from bt_opportunity_case
left join
bt_device_conf on bt_opportunity_case.case_id = bt_device_conf.dev_case_id
left join
bt_case_status on bt_opportunity_case.case_status_id = bt_case_status.STATUS_ID
left join
bt_opportunity on bt_opportunity_case.case_opt_id = bt_opportunity.opt_id
left join
bt_user t1 on t1.user_id = bt_opportunity.OPT_NSM
where case_state='Active'
group by t1.user_last_name, t1.user_first_name

[Dan Bracuk]

Not sure about your specific problem, but since you are summing field from a
left joined table, you should change
sum(bt_opportunity.opt_est_contract_val) as val_total
to sum(coalesce(bt_opportunity.opt_est_contract_val, 0) as val_total

Originally posted by: dcm
Hello,
Below is my current query, I was wondering if anyone could tell me how to sum
the opt_est_contract_val column correctly. The problem is with that query it
repeats that value due to other data points that have to be joined in. I do
have another column called opt_id that would be the logical column to group by
but can't seem to get syntax right. If I group the whole query by opt_id, it
will throw off my other sum. This is an Oracle DB 8.x or 9.x I believe.
Thanks for any help.

select count(*) as total, concat(concat(t1.user_last_name,',
'),t1.user_first_name) as mgr, sum(bt_opportunity.opt_est_contract_val) as
val_total, sum(dev_qty) as dev_total
from bt_opportunity_case
left join
bt_device_conf on bt_opportunity_case.case_id = bt_device_conf.dev_case_id
left join
bt_case_status on bt_opportunity_case.case_status_id = bt_case_status.STATUS_ID
left join
bt_opportunity on bt_opportunity_case.case_opt_id = bt_opportunity.opt_id
left join
bt_user t1 on t1.user_id = bt_opportunity.OPT_NSM
where case_state='Active'
group by t1.user_last_name, t1.user_first_name