Clever Geek Handbook

SQL Aggregates without GROUP BY

I am trying to determine if there is a way to do this elegantly. I have a request:

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 in an elegant way is not need to describe every one of the fields of the TASK table in the GROUP BY (there are about 100 fields and yes, I need all columns).

I also realize that I can move this to the code level and select only the task list and then execute another query to get the cost data. I was hoping to avoid this.

Also, I cannot make it a stored proc because it is not my database.

Any ideas?

Thank.

+2


source to share


4 answers 


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


+4


source 


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


+2


source


Use sub-selection.

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
0


source


For the benefit of anyone looking for this, window functions are a convenient way to do SUM, COUNT, etc. without the need for GROUP BY - if you would otherwise get an error:

"... is not valid in the select list because it is not contained in either an aggregate function or a GROUP BY clause."

Using empty OVER ()

, aggregates span the entire query result set:

SELECT
    Col1,
    Col2,
    SUM(Col3) OVER () AS SumCol3,
    COUNT(Col1) OVER () AS NumRows
FROM Table1
0


source






More articles:


All Articles