CODEWITHSUNDEEP
sqlsql-serversum
Mixer
Mixer

Reputation: 183

SQL Query - Sum at higher level for the same table

I have data in a SQL Server database and this query gives me the top table

SELECT date, resource_name, transfer_resource_name, SUM(calls) as calls 
FROM abc

I could not figure out on how to populate the total_calls column as shown in the bottom table from the picture. Basically, I need to sum the calls and group by select date, resource_name as for total_calls column.

Any inputs will be appreciated!

Here is a picture of the tables

Upvotes: 1

Views: 2186

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

Your query will not work in SQL Server, so I presume that the actual query has a group by.

Then you can do what you want using window functions:

SELECT date, resource_name, transfer_resource_name, SUM(calls) as calls,
       SUM(SUM(calls)) OVER (PARTITION BY resource_name) as total_calls 
FROM abc
GROUP BY date, resource_name, transfer_resource_name;

Upvotes: 5

Samuel Neff
Samuel Neff

Reputation: 74909

use a subquery.

SELECT 
    O.date, 
    O.resource_name, 
    O.transfer_resource_name, 
    SUM(O.calls) as calls,
    (
        SELECT  SUM(I.calls)
        FROM    abc I
        WHERE   I.resource_name = O.resource_name
    ) total_calls    
FROM abc O

Upvotes: 0

Related Questions