SQL Aggregrates without a GROUP BY

Question

I'm trying to determine if there is a way to accomplish this elegantly. I have a query:

SELECT TASK.*, PROJCOST.act_cost, PROJCOST.target_cost
FROM task
LEFT OUTER JOIN projcost ON task.task_id = projcost.task_id

I just found out that PROJCOST.target_cost and act_cost are not 1 to 1 and need to be summed. I understand how to use SUM and GROUP BY, but what I'm trying to find an elegant way NOT to have to spell out every single one of the fields in the TASK table in the GROUP BY (there are close to 100 fields, and yes, I need all columns).

I also understand I could move this up to the code level and only select the task list and then do another query to get the cost data. I was hoping to avoid that.

Also, I cannot make this a stored proc because this isn't my database.

Any ideas?

Thank you.

Answer 1

SELECT TASK.*,
  (select sum (act_cost) from projcost where task_id = task.task_id) as act_cost,
  (select sum (target_cost) from projcost where task_id = task.task_id) as target_cost
FROM task

Answer 2

SELECT TASK.*,
IsNull(allprojcosts.total_act_cost, 0) AS total_act_cost,
IsNull(allprojcosts.total_target_cost, 0) AS total_target_cost
FROM task
LEFT OUTER JOIN (SELECT task_id, Sum(act_cost) AS total_act_cost, Sum(target_cost) AS total_target_cost
    FROM projcost) allprojcosts ON task.task_id = allprojcosts.task_id

Answer 3

Use sub-select.

SELECT TASK.*, sums.* 
FROM 
  task left outer join 
  (
  select task.task_id, sum(PROJCOST.act_cost), sum(PROJCOST.target_cost)
  FROM 
       task LEFT OUTER JOIN 
       projcost ON task.task_id = projcost.task_id
  ) sums ON task.task_id = sums.task_id