Sql Server Aggregate function

Question

I have a select query as,

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

This returns data as, enter image description here

I want to aggregate data as

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

But I want this only for first 7 rows as shown in above image. For more than 7 rows I want the aggregated data in the next row.

David Hedlund · Accepted Answer · 2013-11-21 09:32:09Z

1

How about:

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

answered Nov 21, 2013 at 9:32

David Hedlund

130k33 gold badges205 silver badges223 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeetendra Over a year ago

Really, it worked for exactly 7 rows. But, Would you mind telling for may be 10 or more rows?

David Hedlund Over a year ago

I was under the assumption that you actually wanted to group by week. This gives you grouping by week regardless of whether or not all seven days of any given week are represented in the table. (i.e. if one date is missing, it will not just move along until it reaches seven, but that particular group will be the aggregate of six records). This is not to be construed as an arbitrary clumping function.

David Hedlund Over a year ago

If you do want an arbitrary clumping function, you could perhaps do something with row_number, and grouping over the items that are divisible by the same arbitrary number. Demo

Collectives™ on Stack Overflow

Sql Server Aggregate function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related