CODEWITHSUNDEEP
sqlms-accessgroup-by
user3497492
user3497492

Reputation: 275

SQL GROUP BY and differences on same field (for MS Access)

Hi I have the following style of table under MS Access: (I didn't make the table and cant change it)

Date_r          |  Id_Person |Points |Position
25/05/2015    |  120       | 2000  | 1 
25/05/2015    |  230       | 1500  | 2 
25/05/2015    |  100       |  500  | 3 
21/12/2015    |  120       | 2200  | 1 
21/12/2015    |  230       | 2000  | 4 
21/12/2015    |  100       |  200  | 20

what I am trying to do is to get a list of players (identified by Id_Person) ordered by the points difference between 2 dates.
So for example if I pick date1=25/05/2015 and date2=21/12/2015 I would get:
Id_Person |Points_Diff
230            | 500
120            | 200
100            |-300

I think I need to make something like

SELECT Id_Person , MAX(Points)-MIN(Points)
FROM Table
WHERE date_r = #25/05/2015# or date_r = #21/12/2015#
GROUP BY Id_Person
ORDER BY MAX(Points)-MIN(Points) DESC

But my problem is that i don't really want to order by (MAX(Points)-MIN(Points)) but rather by (points at date2 - points at date1) which can be different because points can decrease with the time.

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

One method is to use first and last However, this can sometimes produce strange results, so I think that conditional aggregation is best:

SELECT Id_Person,
       (MAX(IIF(date_r = #25/05/2015#, Points, 0)) -
        MIN(IIF(date_r = #21/05/2015#, Points, 0))
       ) as PointsDiff
FROM Table
WHERE date_r IN (#25/05/2015#,  #21/12/2015#)
GROUP BY Id_Person
ORDER BY (MAX(IIF(date_r = #25/05/2015#, Points, 0)) -
          MIN(IIF(date_r = #21/05/2015#, Points, 0))
         ) DESC;

Because you have two dates, this is more easily written as:

SELECT Id_Person,
       SUM(IIF(date_r = #25/05/2015#, Points, -Points)) as PointsDiff
FROM Table
WHERE date_r IN (#25/05/2015#,  #21/12/2015#)
GROUP BY Id_Person
ORDER BY SUM(IIF(date_r = #25/05/2015#, Points, -Points)) DESC;

Upvotes: 1

Related Questions