SQL – Aggregate Functions and Group By

MyView:

JobId,
ClientName,
ClientAddress,
Cost

Example data within MyView:

1, John, 23 road, 25
1, John, 23 road, 20
1, John, 23 road, 5
2, James, 6 road, 10
2, James, 6 road, 4

I want to SUM the cost for each JobId, but also return the ClientName, ClientAddress, etc.. This view is large, and having to group the ClientName, Client Address is taking a long time to run.

What is the best way to handle this?

The query I currently have is:

SELECT ClientName,
ClientAddress,
SUM(Cost) as TotalCost
FROM MyView
GROUP BY JobId, ClientName, ClientAddress, TotalCost

Results I’m after:

John, 23 road, 50 
James, 6 road, 14

Answer

If ClientName and ClientAddress are always the same for every JobID, then this (though kludgy) will work:

SELECT MIN(ClientName), MIN(ClientAddress), SUM(Cost) AS TotalCost
  FROM MyView
 GROUP BY JobId

Under that assumption, all you really want to group by is JobID, as everything else is either invariant or an aggregation target.

Advertisement

Answer