CODEWITHSUNDEEP
mysqlsqldjangodjango-modelsdjango-queryset
LondonAppDev
LondonAppDev

Reputation: 9663

Grouping query by similar items in a row

How can I group all items next to each other returned from a query.

It's difficult to explain so best if I just provide an example.

I have a database called UserActions with two columns and the following data:

ID | User | Action
1  | Mark | Jump
2  | Mark | Jump
3  | Mark | Jump
4  | Mark | Run
5  | Mark | Run
6  | John | Run
7  | John | Run
8  | Mark | Run
9  | Mark | Run
10 | Mark | Jump
11 | Mark | Jump
12 | John | Jump
13 | John | Jump

The output I want is this:

Last ID | User | Action   | Count
12      | John | Jump     | 2
10      | Mark | Jump     | 2
8       | Mark | Run      | 2
6       | John | Run      | 2
4       | Mark | Run      | 2
1       | Mark | Jump     | 3

Basically it groups all items by the user and action and outputs the total count before the next row is either a different action or user. If I do regular group by using "annotate" it will just group all items.

Is there a way to do this using a Django Query or raw SQL?

Thanks, Mark

Upvotes: 1

Views: 73

Answers (3)

Strawberry
Strawberry

Reputation: 33945

SELECT x.* 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.user = x.user 
   AND y.action = x.action 
   AND y.id = x.id - 1 
 WHERE y.id IS NULL;

This assumes contiguous incremental ids, as per the example, but it's trivial to rewrite it if that's not the case.

Upvotes: 0

Krzysztof Szularz
Krzysztof Szularz

Reputation: 5249

In django ORM:

(Model.objects.values('user', 'action')
              .order_by()
              .annotate(max_id=models.Max('id'),
                        count=models.Count('action')))

Please note the empty .order_by(). It's needed in order to override one declared in Meta. Django includes default ordering field in GROUP BY fields.

Upvotes: 0

TechGirl
TechGirl

Reputation: 488

SELECT Max(ID),Count([Action]) AS [Count], [User], [Action]
FROM @Table1
GROUP BY [User],[Action]

The above query will yield the desired output.

The Output generated is:

LastID  User    Action  Count
13  John    Jump    2
11  Mark    Jump    5
7   John    Run 2
9   Mark    Run 4

Hope it helps

Upvotes: 1

Related Questions