SQL Server Агрегатная функция

Question

У меня есть запрос на выбор как,

SELECT isnull(T1.TotalShows, 0) AS TotalShows, isnull(T1.ScreenCapacity, 0) AS ScreenCapacity, isnull(T1.ShowDate, 0) AS ShowDate, isnull(T2.TotalTicketsSold, 0) AS TotalTicketsSold, isnull(T2.Nett, 0) AS Nett
FROM (
    SELECT COUNT(showtimeId) AS TotalShows, sum(sc.Capacity) AS ScreenCapacity, ShowDate 
    FROM Shows s
    JOIN Screens sc ON sc.ScreenID = s.ScreenID
    WHERE s.MovieID = 34
        AND s.IsDeleted = 0
    GROUP BY ShowDate
    ) AS T1
LEFT OUTER JOIN (
    SELECT s.ShowDate, COUNT(ut.UserTicketID) AS TotalTicketsSold, SUM(ISNULL((Price + ConvinienceCharge - DiscountAmount) / (EntertainmentTax + BoxOfficeTax + 1), 0)) AS Nett
    FROM Shows s
    LEFT OUTER JOIN UserTickets ut ON s.ShowID = ut.ShowID
    WHERE ut.ShowID IN (
            SELECT ShowID
            FROM Shows
            WHERE MovieID = 34
                AND IsDeleted = 0
            GROUP BY ShowID
            )
    GROUP BY s.ShowDate
    ) AS T2 ON T1.ShowDate = T2.ShowDate

Это возвращает данные как,

Я хочу агрегировать данные как

SUM(totalShows),SUM(ScreenCapacity),MAX(ShowDate),SUM(TotalTicketsSold),SUM(Nett)

Но я хочу это только для первых 7 строк, как показано на рисунке выше. Для более чем 7 строк я хочу агрегированные данные в следующей строке.

Answer 1

Как насчет:

GROUP BY DATEPART(YEAR, ShowDate), DATEPART(WEEK, ShowDate)