CODEWITHSUNDEEP
sqlsql-servergroup-bysum
Grzegorz Fedczyszyn
Grzegorz Fedczyszyn

Reputation: 344

sql sum group by

I have following sql query

  DECLARE @frameInt INT

SELECT TOP 1 @frameInt = gs.FrameGenerationInterval
FROM dbo.GlobalSettings gs

SELECT mti.InternalId,
        b.InternalId AS BrandId,
        CASE 
            WHEN DATEDIFF(second, mti.StartTime, mti.EndTime) / @frameInt > 0 THEN DATEDIFF(second, mti.StartTime, mti.EndTime) / @frameInt
            ELSE 1
        END AS ExposureAmount,
        c.InternalId AS ChannelId,
        c.Name AS ChannelName,

        COALESCE( (p.Rating * 
            CASE 
                WHEN DATEDIFF(second, mti.StartTime, mti.EndTime) / @frameInt > 0 THEN DATEDIFF(second, mti.StartTime, mti.EndTime) / @frameInt
                ELSE 1
            END * CAST (17.5 AS decimal(8,2))
        ),CAST( 0 as decimal(8,2)) ) AS Equivalent


    FROM dbo.MonitorTelevisionItems mti
        LEFT JOIN dbo.Brands b ON mti.BrandId = b.InternalId
        LEFT JOIN dbo.Channels c ON mti.ChannelId = c.InternalId
        LEFT JOIN dbo.Programs p ON mti.ProgramId = p.InternalId
    --WHERE mti.Date >= @dateFromLocal AND mti.Date <= @dateToLocal
    GROUP BY mti.InternalId,  mti.EndTime, mti.StartTime,
        c.Name, p.Name, p.Rating,b.InternalId,  c.InternalId

It gives following result

result

I would like it to return 1 row with sums of exposure amount and equivalent from all rows. Rest of the cells are the same apart from InternalId that I dont really need (i can remove it from query)

I am not very good at sql. Thank for help.

Upvotes: 1

Views: 129

Answers (1)

Heinzi
Heinzi

Reputation: 172478

For the sake of posterity (and because it's cool to learn something new), here's the general solution to your problem (instead of a copy-and-paste ready solution for your specific case):

SELECT group_field1, group_field2, ..., 
       SUM(sum_field1), SUM(sum_field2), ...

  FROM (...your original SQL...) AS someArbitraryAlias

 GROUP BY group_field1, group_field2, ...

In your specific case, the group fields would be BrandId, ChannelId and ChannelName; the sum fields would be ExposureAmount and Equivalent.

Note: To ease readability (since your original SQL is quite complex), you can use a common table expression:

WITH someArbitraryAlias AS (
    ...your original SQL...
)

SELECT group_field1, group_field2, ..., 
       SUM(sum_field1), SUM(sum_field2), ...

  FROM someArbitraryAlias

 GROUP BY group_field1, group_field2, ...

Note that, when using common table expressions, the immediately preceding statement must be terminated with a semicolon.

Upvotes: 2

Related Questions