Sql-server – How to get Row Value & Sum/ Aggregate in Same row? (SQL Server)

aggregateselectsql server

Just wondering if I have data like

 name  score
    A    2
    B    5
    C    7

I would like to have a 3rd row

     name  score  total
        A    2     14
        B    5     14
        C    7     14

I mean ultimately — I want to find 'z-scores' or percentiles of various row entries across various totals. I'm just not sure if there's a simple way to compare row values to aggregate values, except by a subquery such as

select A.name, A.score, B.total
from myTable A
left join
(select name, sum(score) from myTable group by name) B
on A.name = B.name

That could work but it seems inefficient querying the same table twice for what is a calculation. Is there any simpler way of doing it that I'm missing?

Best Answer

Yes, there is a way. It's called "window aggregate functions":

select
    A.name, A.score, 
    sum(A.score) over () as total
from myTable A ;

The OVER () means that the aggregate (sum in this case) is over the whole result set. If you wanted the total over say different names (assuming you had rows with same name, you could use OVER (PARTITION BY name).

Related Solutions

Sql-server – Get ID of row in aggregate

You can use window (ranking). functions for this:

WITH ct AS
  ( SELECT X, Y, RowAddedDate, ID,
           Rn = DENSE_RANK() OVER (PARTITION BY X, Y
                                   ORDER BY RowAddedDate DESC)
    FROM dbo.MyTable
  )
sELECT X, Y, RowAddedDate, ID
FROM ct 
WHERE Rn = 1 ;

If multiple rows have the same date with same X and Y, all of them will be returned due to the DENSE_RANK().

If you want only one, you can replace it with ROW_NUMBER() and adjust the ORDER BY to control which of the tied rows will be returned.

As for efficiency, an index on (X, Y, RowAddedDate) INCLUDE (ID) would help.

Sql-server – Select command returning different column values

The query you wrote is basically asking the database to give you any two rows of data (any two orders). If you wrote the query with an ORDER BY clause then your results should be the same each time.

SELECT TOP 2 OrderID FROM Sales.Orders ORDER BY OrderID ASC;

This would give you the two rows with the lowest OrderID and you will still get the same two rows even when you add other fields to the SELECT list.

You might be thinking that TOP means that the database will give you the first two, but it actually means that it should stop returning rows after it has returned two rows. And since you didn't have an ORDER BY it could just return whichever two rows are most convenient.

Best Answer

Related Solutions

Sql-server – Get ID of row in aggregate

Sql-server – Select command returning different column values

Related Question