SQL Aggregation Query

Question

I have a dataset that I need to do some aggregation on to display.

Date,     Rank, Vote

07/20/2013, 8, -1 
07/21/2013, 8, -1 
07/23/2013, 7, -1 
07/24/2013, 6, 1
07/25/2013, 7, 1 
07/26/2013, 7, -1 
07/27/2013, 8, 1 
07/28/2013, 8, 1
07/29/2013, 8, -1

I'd like to group by consecutive ranks, summing the vote, and choosing the first of the grouped dates. The above data set would return:

07/20/2013,8,-2
07/23/2013,7,-1
07/24/2013,6,1
07/25/2013,7,0
07/27/2013,8,1

My first thought was GROUP BY Date and Rank but that wouldn't consolidate multiple days. I can do this after the fact by looping thru the data or I can use a cursor within a stored procedure but I'm curious if there is a simpler way with a SQL query.

Dates use a date datatype, right?

Strawberry
– Strawberry

2013-07-26 16:35:31 +00:00
Commented Jul 26, 2013 at 16:35 — Strawberry
– Strawberry, Commented Jul 26, 2013 at 16:35

Barmar · Accepted Answer · 2013-07-26 16:58:06Z

2

This does it:

SELECT firstDate, Rank, SUM(vote) Votes
FROM (
    SELECT @first_date := CASE WHEN Rank = @prev_rank
                               THEN @first_date
                               ELSE Date
                          END firstDate,
           @prev_rank := Rank curRank,
           data.*
    FROM (SELECT @first_date := NULL, @prev_rank := NULL) init
    JOIN (SELECT Date, Rank, Vote
          FROM MyTable
          Order by Date) data
    ) grouped
GROUP BY firstDate, Rank

SQLFIDDLE

edited Jul 26, 2013 at 16:58

answered Jul 26, 2013 at 16:35

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

Comments

Fabian Bigler · Accepted Answer · 2013-07-26 16:47:08Z

0

Most straightforward way I can see, is what you already pointed out.

Use the Group By SQL:

SELECT  date, rank, SUM(vote) FROM YourTable
GROUP BY date, rank

Fiddle Demo: http://sqlfiddle.com/#!2/d65d5c/3

Iterate through this record set in your program and do what is needed to get your data. (If you tag your question with a programming language, I can show you how).

So basically no, I can't see any better way to do this. As far I can see this would end up in a fairly complicated and slow SQL query. But maybe someone can teach me better.

edited Jul 26, 2013 at 16:47

answered Jul 26, 2013 at 16:38

Fabian Bigler

10.9k6 gold badges52 silver badges74 bronze badges

4 Comments

Barmar Over a year ago

he said he knows how to do it by looping in the programming language, his question is if there's a way to do the extra grouping entirely in SQL.

Fabian Bigler Over a year ago

Yes, and my answer is to do it this way. Grouping in SQL and do the rest in the RAM.

Barmar Over a year ago

So your answer is "no"? I'm pretty sure it can be done in SQL using extra variables, similar to the methods for calculating rank in a group.

Fabian Bigler Over a year ago

@Barmar I think this is the most performant and most readable way to do this. Of course you could solve this entirely in SQL. But I suppose solely grouping it and iterate through it in the program is faaaar easier to maintain and more performant. I'm curious if anybody comes up with a better solution, though.

Collectives™ on Stack Overflow

SQL Aggregation Query

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related