T-sql – GROUP BY multiple criteria with difference in row fields within a group

group byjoin;t-sql

Everyone,

I am struggling to find a solution for the following task:

All users (1) that have a game (2) with their items count (3) and items price (4) are to be retrieved by a query 
=> Display the username, game name, items count and items price.

I have made the necessary JOINs correctly. I won't bother you with the schema and the particular tables and their corresponding tuples. Instead, please find below the first result of the above "raw-dataset-JOIN" query. I ask your help since it is interesting to me how to group the results by Username and Name showing only a single row for the multiple occurrences of "username+name". I seem not to be able to get it as the items are varying across the repetitive "username+name" occurrences and for MS SQL Server obviously the data is not identical so that it can be "grouped" properly?

Should the result be obtained by the usage of a VIEW or Sub-query?

Please assist – appreciate your help!

SELECT Users.[Username], Games.[Name], COUNT(Items.[Name]), Items.Price FROM UsersGames
LEFT OUTER JOIN Games
ON Games.[Id] = UsersGames.GameId
JOIN UserGameItems
ON UserGameItems.UserGameId = UsersGames.[Id]
JOIN Items 
ON Items.[Id] = UserGameItems.[ItemId] 
JOIN Users
ON Users.Id = UsersGames.UserId
GROUP BY Users.[Username], Games.[Name], Items.Price

Username                                           Name                                               Count       Item Name                                          Item Price
-------------------------------------------------- -------------------------------------------------- ----------- -------------------------------------------------- ---------------------
admincuttle                                        Flax New Zealand                                   1           Cosmic Strand                                      243,00
admincuttle                                        Flax New Zealand                                   1           Griswolds Masterpiece                              342,00
admincuttle                                        Flax New Zealand                                   1           Simplicitys Strength                               29,00
admincuttle                                        Flax New Zealand                                   1           Staff of Herding                                   42,00
admincuttle                                        Flax New Zealand                                   1           Wall of Bone                                       537,00
admincuttle                                        Florence                                           1           Esoteric Alteration                                675,00
admincuttle                                        Florence                                           1           Eye of Peshkov                                     772,00
admincuttle                                        Florence                                           1           Hierophants Seal                                   146,00
admincuttle                                        Florence                                           1           Starfire                                           423,00
admincuttle                                        Freesia                                            1           Blessed of Haull                                   318,00
admincuttle                                        Freesia                                            1           Hellfire Ring (Dexterity)                          29,00
admincuttle                                        Freesia                                            1           Razor Strop                                        396,00
admincuttle                                        Moon orchid                                        1           Blackfeather                                       442,00
admincuttle                                        Moon orchid                                        1           Chanon Bolter                                      272,00
admincuttle                                        Moon orchid                                        1           Cloaks                                             249,00
admincuttle                                        Moon orchid                                        1           Demon Hand                                         609,00
admincuttle                                        Moon orchid                                        1           Gesture of Orpheus                                 365,00
admincuttle                                        Moon orchid                                        1           Mad Monarchs Scepter                               370,00
admincuttle                                        Moon orchid                                        1           Shattered Core                                     180,00
admincuttle                                        Moon orchid                                        1           Silver Star Piercers                               265,00
admincuttle                                        Moon orchid                                        1           The Horadric Hamburger                             762,00
admincuttle                                        Moon orchid                                        1           Tiklandian Visage                                  665,00
admincuttle                                        Moon orchid                                        1           Two-Handed Flails                                  583,00
admincuttle                                        Paris                                              1           Adventurers Journal                                720,00
admincuttle                                        Paris                                              1           Blessed of Haull                                   318,00
admincuttle                                        Paris                                              1           Blitzbolter                                        18,00
admincuttle                                        Paris                                              1           Dawn                                               96,00
admincuttle                                        Paris                                              1           Greater Rift Keystone                              396,00
admincuttle                                        Paris                                              1           Ramaladnis Gift                                    326,00
admincuttle                                        Paris                                              1           Sunder                                             668,00
admincuttle                                        Paris                                              1           Tyraels Might (Diablo III)                         607,00
admincuttle                                        Paris                                              1           Wizard Hats                                        121,00
advisersspry                                       Ablajeck                                           1           Alabaster Gloves                                   65,00
advisersspry                                       Ablajeck                                           1           Bombardiers Rucksack                               582,00
advisersspry                                       Ablajeck                                           1           Calamity                                           68,00
advisersspry                                       Ablajeck                                           1           Deaths Bargain                                     354,00
advisersspry                                       Ablajeck                                           1           Denial                                             164,00
advisersspry                                       Ablajeck                                           1           Gloves                                             103,00
advisersspry                                       Ablajeck                                           1           Rozpedins Force                                    690,00
advisersspry                                       Ablajeck                                           1           Thing of the Deep                                  799,00
advisersspry                                       Ablajeck                                           1           Warmonger                                          773,00
advisersspry                                       Allied Tion                                        1           Ahavarion, Spear of Lycander                       313,00
advisersspry                                       Allied Tion                                        1           Bottomless Potion of the Diamond                   615,00
advisersspry                                       Allied Tion                                        1           Broken Crown                                       237,00
advisersspry                                       Allied Tion                                        1           Devil Tongue                                       584,00
advisersspry                                       Allied Tion                                        1           Maces                                              760,00
advisersspry                                       Allied Tion                                        1           Maloths Focus                                      743,00
advisersspry                                       Allied Tion                                        1           Two-Handed Mighty Weapons                          283,00
advisersspry                                       Allied Tion                                        1           Wands                                              16,00
advisersspry                                       Heather                                            1           Flying Dragon                                      621,00
advisersspry                                       Heather                                            1           Giant Skull (Diablo III)                           2,00
advisersspry                                       Heather                                            1           Nailbiter                                          123,00
advisersspry                                       Heather                                            1           Robes of the Rydraelm                              208,00
advisersspry                                       Heather                                            1           Steady Strikers                                    581,00
advisersspry                                       Heather                                            1           Telrandens Hand                                    303,00
advisersspry                                       Heather                                            1           Warmonger                                          773,00
advisersspry                                       Heather                                            1           Wizard Hats                                        121,00
advisersspry                                       Heather                                            1           Wondrous Deflectors                                137,00
advisersspry                                       Rose Bridal White                                  1           Buriza-Do Kyanon (Diablo III)                      713,00
advisersspry                                       Rose Bridal White                                  1           Goldskin (Diablo III)                              449,00
advisersspry                                       Rose Bridal White                                  1           Haunting Girdle                                    618,00
advisersspry                                       Rose Bridal White                                  1           Helms                                              219,00
advisersspry                                       Rose Bridal White                                  1           The Furnace                                        223,00
advisersspry                                       Rose Bridal White                                  1           Valtheks Rebuke                                    573,00
advisersspry                                       Rose Bridal White                                  1           Warstaff of General Quang                          490,00
advisersspry                                       Rose Bridal White                                  1           Ziggurat Tooth                                     788,00
Alex                                               Edinburgh                                          1           Akanesh, the Herald of Righteousness               586,00
Alex                                               Edinburgh                                          1           Band of Hollow Whispers                            405,00
Alex                                               Edinburgh                                          1           Drakons Lesson                                     341,00
Alex                                               Edinburgh                                          1           Nailbiter                                          123,00
Alex                                               Edinburgh                                          1           Robes of the Rydraelm                              208,00
Alex                                               Edinburgh                                          1           Skull of Resonance                                 45,00
Alex                                               Edinburgh                                          1           Vile Ward                                          516,00
Alex                                               Edinburgh                                          1           Voodoo Masks                                       117,00
Alex                                               Flax New Zealand                                   1           Akarats Awakening                                  80,00
Alex                                               Flax New Zealand                                   1           Board Walkers                                      477,00
Alex                                               Flax New Zealand                                   1           Boon of the Hoarder                                208,00
Alex                                               Flax New Zealand                                   1           Bows                                               364,00
Alex                                               Flax New Zealand                                   1           Maras Kaleidoscope (Diablo III)                    791,00
Alex                                               Flax New Zealand                                   1           Ring of Royal Grandeur                             554,00
Alex                                               Flax New Zealand                                   1           Spite                                              80,00
Alex                                               Flax New Zealand                                   1           The Swami                                          211,00
Alex                                               Flax New Zealand                                   1           White Oak Splinter                                 36,00
Alex                                               Madrid                                             1           Eternal Union                                      47,00
Alex                                               Madrid                                             1           Homunculus (Diablo III)                            517,00
Alex                                               Madrid                                             1           Madstone                                           795,00
Alex                                               Madrid                                             1           Shields                                            380,00
Alex                                               Madrid                                             1           Thing of the Deep                                  799,00
Alex                                               Pink Mink                                          1           Alabaster Gloves                                   65,00
Alex                                               Pink Mink                                          1           Bezoar Stone                                       510,00
Alex                                               Pink Mink                                          1           Key of Bones                                       452,00
Alex                                               Pink Mink                                          1           Moratorium                                         658,00
Alex                                               Pink Mink                                          1           Sky Splitter                                       14,00
Alex                                               Singapore                                          1           Rydraelm Tome                                      625,00
Alex                                               Singapore                                          1           Shields                                            380,00
Alex                                               Singapore                                          1           The Three Hundredth Spear                          732,00
Alex                                               Singapore                                          1           Two-Handed Flails                                  583,00
baleremuda                                         Alderaan                                           1           Bane of the Stricken                               445,00
baleremuda                                         Alderaan                                           1           Blood-Magic Blade                                  529,00
baleremuda                                         Alderaan                                           1           Drakons Lesson                                     341,00
baleremuda                                         Alderaan                                           1           Essence of the Twin Seas                           468,00
baleremuda                                         Alderaan                                           1           Hilt of the Silver Wolf                            26,00
baleremuda                                         Alderaan                                           1           Incense Torch of the Grand Temple                  533,00
baleremuda                                         Alderaan                                           1           Pig Sticker                                        189,00
baleremuda                                         Alderaan                                           1           Rabid Strike                                       231,00
baleremuda                                         Alderaan                                           1           The Inquisitor                                     590,00
baleremuda                                         Alderaan                                           1           Visage of Giyua                                    167,00
baleremuda                                         Bali                                               1           Mojos                                              604,00
baleremuda                                         Bali                                               1           Ramaladnis Gift                                    326,00
baleremuda                                         Bali                                               1           Rings                                              673,00
baleremuda                                         Bali                                               1           The Grin Reaper                                    375,00
baleremuda                                         Foxtail fern                                       1           Echoing Fury                                       354,00
baleremuda                                         Foxtail fern                                       1           Legendary Gems                                     244,00
baleremuda                                         Foxtail fern                                       1           List of Legendary Items                            67,00
baleremuda                                         Foxtail fern                                       1           Prides Fall                                        465,00
baleremuda                                         Foxtail fern                                       1           Sacred Harvester                                   678,00
baleremuda                                         Foxtail fern                                       1           The Spider Queen’s Grasp                           326,00
baleremuda                                         Kiev                                               1           Bottomless Potion of Amplification                 90,00
baleremuda                                         Kiev                                               1           Emimeis Duffel                                     375,00
baleremuda                                         Kiev                                               1           Erlang Shen                                        105,00
baleremuda                                         Kiev                                               1           Fire Brand                                         235,00
baleremuda                                         Kiev                                               1           Manticore                                          243,00
baleremuda                                         Kiev                                               1           Spirit Guards                                      775,00
baleremuda                                         São Paulo                                          1           Cindercoat                                         326,00
baleremuda                                         São Paulo                                          1           Esoteric Alteration                                675,00
baleremuda                                         São Paulo                                          1           Oculus Ring                                        799,00
baleremuda                                         São Paulo                                          1           Serpents Sparker                                   354,00
baleremuda                                         São Paulo                                          1           The Grand Vizier                                   298,00
baroquegainful                                     Kuala Lumpur                                       1           Cloaks                                             249,00
baroquegainful                                     Kuala Lumpur                                       1           Gift of Silaria                                    527,00
baroquegainful                                     Kuala Lumpur                                       1           Mad Monarchs Scepter                               370,00
baroquegainful                                     Kuala Lumpur                                       1           Pauldrons of the Skeleton King                     183,00
baroquegainful                                     Kuala Lumpur                                       1           String of Ears (Diablo III)                        113,00
baroquegainful                                     London                                             1           Blind Faith                                        528,00
baroquegainful                                     London                                             1           Bottomless Potion of the Diamond                   615,00
baroquegainful                                     London                                             1           Captured Soul                                      380,00
baroquegainful                                     London                                             1           Cord of the Sherma                                 739,00
baroquegainful                                     London                                             1           Earthshatter                                       160,00
baroquegainful                                     London                                             1           Flails                                             278,00
baroquegainful                                     London                                             1           Golden Scourge                                     477,00
baroquegainful                                     London                                             1           Ice Climbers                                       166,00
baroquegainful                                     London                                             1           Longshot                                           565,00
baroquegainful                                     London                                             1           Pledge of Caldeum                                  193,00
baroquegainful                                     London                                             1           Sever                                              782,00
baroquegainful                                     London                                             1           The Ess of Johan                                   613,00
baroquegainful                                     London                                             1           Vengeful Wind                                      507,00
baroquegainful                                     London                                             1           Wooden Stake                                       96,00
baroquegainful                                     Macau                                              1           Angelic Shard                                      517,00
baroquegainful                                     Macau                                              1           Etrayu                                             576,00
baroquegainful                                     Macau                                              1           Jaces Hammer of Vigilance                          410,00
baroquegainful                                     Macau                                              1           Penders Purchase                                   232,00
baroquegainful                                     Macau                                              1           Split Tusk                                         717,00
baroquegainful                                     Seoul                                              1           Belt of the Trove                                  387,00
baroquegainful                                     Seoul                                              1           Empyrean Messenger                                 503,00
baroquegainful                                     Seoul                                              1           Giant Skull (Diablo III)                           2,00
baroquegainful                                     Seoul                                              1           Greater Rift Keystone                              396,00
baroquegainful                                     Seoul                                              1           Hand Crossbows                                     302,00
baroquegainful                                     Seoul                                              1           Inviolable Faith                                   305,00
baroquegainful                                     Seoul                                              1           Mutilation Guard                                   500,00
baroquegainful                                     Seoul

The correct Output should like this:

P.s. With the below w/o Items.Price in the SELECT the usernames are grouped by correctly, I am pasting a screenshot since the 30.000 characters mark are hit otherwise:

Best Answer

Unless I have misunderstood, the desired output is the username, game, total number of items bought, and total price paid for the items. This can be obtained by counting the items and summing the price, like so:

   SELECT u.[Username]
        , g.[Name]
        , COUNT(i.[Name]) AS [Items Count]
        , SUM(i.Price) AS [Items Price] 
     FROM UsersGames AS ug
LEFT JOIN Games AS g
       ON g.[Id] = ug.[GameId]
     JOIN UserGameItems AS ugi
       ON ugi.[UserGameId] = ug.[Id]
     JOIN Items AS i
       ON i.[Id] = ugi.[ItemId] 
     JOIN Users AS u
       ON u.Id = ug.UserId
 GROUP BY u.[Username], g.[Name];

To get a single price per username and game when you include the item price in the group by would require all prices must be the same. However, this is not what you have because you do not want to include the actual item details in the result set. Therefore, removing the item price from the group by and then summing the column should give you your desired result.

Related Solutions

Sql-server – how to calculate difference of first row and last row fields value in each group

You are not showing the query you are using to obtain the results without diff. I'm assuming it is something like this:

SELECT
  min  = MIN(Value),
  max  = MAX(Value),
  avg  = AVG(Value),  -- or, if Value is an int, like this, perhaps:
                      -- AVG(CAST(Value AS decimal(10,2))
  Date = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
FROM atable
GROUP BY
  DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
;

Also, you are not explaining what first and last mean. In this answer, it is assumed that first stands for earliest in the group (according to the Date value) and, similarly, last means latest in the group.

One way to throw in diff could be like this:

First, add two more aggregated columns, minDate and maxDate, to the original query:

SELECT
  min     = MIN(Value),
  max     = MAX(Value),
  avg     = AVG(Value),
  minDate = MIN(Date),
  maxDate = MAX(Date),
  Date    = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
FROM atable
GROUP BY
  DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
;

Next, join the aggregated result set back to the original table on minDate and on maxDate (separately) to access the corresponding Values:

SELECT
  g.min,
  g.max,
  g.avg,
  diff = last.Value - first.Value,
  g.Date
FROM (
  SELECT
    min     = MIN(Value),
    max     = MAX(Value),
    avg     = AVG(Value),
    minDate = MIN(Date),
    maxDate = MAX(Date),
    Date    = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
  FROM atable
  GROUP BY
    DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
) g
INNER JOIN atable first ON first.Date = g.minDate
INNER JOIN atable last  ON last .Date = g.maxDate
;

Note that the above assumes the Date values (at least those that happen to be first or last in their corresponding hours) to be unique, or you would get more than one row for some of the hours in the output.

An alternative, if you are on SQL Server 2005 or later version, could be to use window aggregate functions MIN() OVER (...) and MAX() OVER (...) to calculate Values corresponding to either minDate or maxDate, before aggregating all the results similarly to how you are probably doing it now. Here's what specifically I'm talking about:

WITH partitioned AS (
  SELECT
    Value,
    Date,
    GroupDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
  FROM atable
)
, firstlast AS (
  SELECT
    Value,
    Date,
    GroupDate,
    FirstValue = CASE Date WHEN MIN(Date) OVER (PARTITION BY GroupDate) THEN Value END,
    LastValue  = CASE Date WHEN MAX(Date) OVER (PARTITION BY GroupDate) THEN Value END
  FROM partitioned
)
SELECT
  min  = MIN(Value),
  max  = MAX(Value),
  avg  = AVG(Value),  -- or, again, if Value is an int, cast it as a decimal or float
  diff = MAX(LastValue) - MIN(FirstValue),
  Date = GroupDate
FROM firstlast
GROUP BY
  GroupDate
;

As you can see, the first common table expression (CTE) merely returns all rows and adds a calculated column GroupDate, the one subsequently used for grouping/partitioning. So it essentially just assigns a name to the grouping expression, and that is done for better readability/maintainability of the entire query, as the column is later referenced more than once. This is what the first CTE produces:

+-------+------------------+------------------+
| Value |       Date       |    GroupDate     |
+-------+------------------+------------------+
|    10 | 10/10/2010 10:00 | 10/10/2010 10:00 |
|    11 | 10/10/2010 10:15 | 10/10/2010 10:00 |
|    15 | 10/10/2010 10:30 | 10/10/2010 10:00 |
|    15 | 10/10/2010 10:45 | 10/10/2010 10:00 |
|    17 | 10/10/2010 11:00 | 10/10/2010 11:00 |
|    18 | 10/10/2010 11:15 | 10/10/2010 11:00 |
|    22 | 10/10/2010 11:30 | 10/10/2010 11:00 |
|    30 | 10/10/2010 11:45 | 10/10/2010 11:00 |
+-------+------------------+------------------+

The second CTE adds two more columns to the above result. It uses window aggregate functions MIN() OVER ... and MAX() OVER ... to match against Date, and where the match takes place, the corresponding Value is returned in a separate column, either FirstValue or LastValue:

+-------+------------------+------------------+------------+-----------+
| Value |       Date       |    GroupDate     | FirstValue | LastValue |
+-------+------------------+------------------+------------+-----------+
|    10 | 10/10/2010 10:00 | 10/10/2010 10:00 |         10 |      NULL |
|    11 | 10/10/2010 10:15 | 10/10/2010 10:00 |       NULL |      NULL |
|    15 | 10/10/2010 10:30 | 10/10/2010 10:00 |       NULL |      NULL |
|    15 | 10/10/2010 10:45 | 10/10/2010 10:00 |       NULL |        15 |
|    17 | 10/10/2010 11:00 | 10/10/2010 11:00 |         17 |      NULL |
|    18 | 10/10/2010 11:15 | 10/10/2010 11:00 |       NULL |      NULL |
|    22 | 10/10/2010 11:30 | 10/10/2010 11:00 |       NULL |      NULL |
|    30 | 10/10/2010 11:45 | 10/10/2010 11:00 |       NULL |        30 |
+-------+------------------+------------------+------------+-----------+

At this point, everything is ready for the final aggregation. The min, max, and avg columns will be aggregated same as previously, and diff can now easily be obtained as the aggregated FirstValue subtracted from the aggregated LastValue. As you can see from the above result set, you can use various functions to get FirstValue and LastValue for the group: it could be MIN, MAX, SUM, AVG – any if these would do, because there's just one value in every group.

The main SELECT, however, as you can see, applies specifically MAX() over LastValue and MIN() over FirstValue. That is intentional. It's because this second suggestion doesn't really require Date to be unique, like the first one did, but, in case either minDate or maxDate does happen to have more than one associated Value, it would result in FirstValue or LastValue containing more than one value per group, something like this:

+-------+------------------+------------------+------------+-----------+
| Value |       Date       |    GroupDate     | FirstValue | LastValue |
+-------+------------------+------------------+------------+-----------+
|     9 | 10/10/2010 10:00 | 10/10/2010 10:00 |          9 |      NULL |
|    10 | 10/10/2010 10:00 | 10/10/2010 10:00 |         10 |      NULL |
|    11 | 10/10/2010 10:15 | 10/10/2010 10:00 |       NULL |      NULL |
|    15 | 10/10/2010 10:30 | 10/10/2010 10:00 |       NULL |      NULL |
|    15 | 10/10/2010 10:45 | 10/10/2010 10:00 |       NULL |        15 |
|    17 | 10/10/2010 11:00 | 10/10/2010 11:00 |         17 |      NULL |
|    18 | 10/10/2010 11:15 | 10/10/2010 11:00 |       NULL |      NULL |
|    22 | 10/10/2010 11:30 | 10/10/2010 11:00 |       NULL |      NULL |
|    30 | 10/10/2010 11:45 | 10/10/2010 11:00 |       NULL |        30 |
|    33 | 10/10/2010 11:45 | 10/10/2010 11:00 |       NULL |        33 |
+-------+------------------+------------------+------------+-----------+

I assumed that in this situation it would be more natural to take the difference between the greatest last value and the least first one. You, however, should know better what rule to apply here, so you'll just change the query accordingly.

You can test both solutions at SQL Fiddle:

Update

Starting from SQL Server 2012, you can also use the FIRST_VALUE and LAST_VALUE functions and substitute them for the CASE expressions in the firstlast CTE in my last query above, like this:

FirstValue = FIRST_VALUE(Value) OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
LastValue  = LAST_VALUE(Value)  OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

In this case it will not matter whether you use MIN or MAX over FirstValue and LastValue later (in the main SELECT): each column will have exactly the same value (first or last Value accordingly) across all rows of the same GroupDate group, and so MIN() and MAX() would return identical results in each case.

In fact, you can get diff directly in the firstlast CTE and then, in the main query, just either aggregate it using MIN/MAX or add it to GROUP BY and reference it without aggregation, like this:

WITH partitioned AS (
  SELECT
    Value,
    Date,
    GroupDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
  FROM atable
)
, firstlast AS (
  SELECT
    Value,
    Date,
    GroupDate,
    diff = LAST_VALUE(Value)  OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
         - FIRST_VALUE(Value) OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  FROM partitioned
)
SELECT
  min  = MIN(Value),
  max  = MAX(Value),
  avg  = AVG(Value),
  diff,
  Date = GroupDate
FROM firstlast
GROUP BY
  GroupDate,
  diff
;

Moving one step further, you could obtain min, max and avg in firstlast as well, instead of the main query – using the counterpart window functions:

min  = MIN(Value) OVER (PARTITION BY GroupDate),
max  = MAX(Value) OVER (PARTITION BY GroupDate),
avg  = AVG(Value) OVER (PARTITION BY GroupDate),

With these three additional columns and the previous change, the firstlast CTE will be returning a row set like this for your example:

+-------+------------------+------------------+-----+-----+-------+------+
| Value |       Date       |    GroupDate     | min | max |  avg  | diff |
+-------+------------------+------------------+-----+-----+-------+------+
|    10 | 10/10/2010 10:00 | 10/10/2010 10:00 |  10 |  15 | 12.75 |    5 |
|    11 | 10/10/2010 10:15 | 10/10/2010 10:00 |  10 |  15 | 12.75 |    5 |
|    15 | 10/10/2010 10:30 | 10/10/2010 10:00 |  10 |  15 | 12.75 |    5 |
|    15 | 10/10/2010 10:45 | 10/10/2010 10:00 |  10 |  15 | 12.75 |    5 |
|    17 | 10/10/2010 11:00 | 10/10/2010 11:00 |  17 |  30 | 21.75 |   13 |
|    18 | 10/10/2010 11:15 | 10/10/2010 11:00 |  17 |  30 | 21.75 |   13 |
|    22 | 10/10/2010 11:30 | 10/10/2010 11:00 |  17 |  30 | 21.75 |   13 |
|    30 | 10/10/2010 11:45 | 10/10/2010 11:00 |  17 |  30 | 21.75 |   13 |
+-------+------------------+------------------+-----+-----+-------+------+

Note how GroupDate, min, max, avg and diff – the columns that you really need for the final set – are simply repeated across all rows that belong to the same group. That means that you can get rid of Value and Date, rename GroupDate to Date, slightly rearrange the columns, apply DISTINCT to the resulting set – and you have eliminated the last SELECT:

WITH partitioned AS (
  SELECT
    Value,
    Date,
    GroupDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
  FROM
    atable
)
SELECT DISTINCT
  min  = MIN(Value) OVER (PARTITION BY GroupDate),
  max  = MAX(Value) OVER (PARTITION BY GroupDate),
  avg  = AVG(Value) OVER (PARTITION BY GroupDate),
  diff = LAST_VALUE(Value)  OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       - FIRST_VALUE(Value) OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
  Date = GroupDate
FROM
  partitioned
;

Finally, it is also possible to move the GroupDate calculation into the same scope where min, max etc. are calculated. You can use CROSS APPLY for that and thus avoid the need for nesting a query entirely – in other words, this way you can get rid of the partitioned CTE as well. The entire query would look like this:

SELECT DISTINCT
  min  = MIN(t.Value) OVER (PARTITION BY x.GroupDate),
  max  = MAX(t.Value) OVER (PARTITION BY x.GroupDate),
  avg  = AVG(t.Value) OVER (PARTITION BY x.GroupDate),
  diff = LAST_VALUE(t.Value)  OVER (PARTITION BY x.GroupDate ORDER BY t.Date ASC
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       - FIRST_VALUE(t.Value) OVER (PARTITION BY x.GroupDate ORDER BY t.Date ASC
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
  Date = x.GroupDate
FROM
  atable AS t
  CROSS APPLY (SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)) AS x (GroupDate)
;

and return the same results. You can test it at SQL Fiddle too.

Mysql – Is it possible to reduce a group to a row based on some criteria

The traditional solution, the one you may find in books, is to do a self join: first find that "max date per group" of yours, then join to self table on rows with said max date.

However, some hacks allow you to avoid that. Consider the following query:

SELECT
  MAX(date_added) AS date_added,
  SUBSTRING_INDEX(GROUP_CONCAT(some_column ORDER BY date_added DESC), ',', 1) AS some_column,
  SUBSTRING_INDEX(GROUP_CONCAT(another_column ORDER BY date_added DESC), ',', 1) AS another_column
FROM t
GROUP BY whatever

GROUP_CONCAT is an aggregation function which implodes values onto one string. It allows for ORDER BY, which we utilize via ORDER BY date_added DESC so as to implode our desired value first. We then slice up the first token in the string via SUBSTRING_INDEX.

The downside here (apart from making the query quite the frightening appearance) is that your numerical values are transformed into texts. Typically no big deal with SQL, but please be aware.

There's another option where you do a semi-self-join, a much lighter one; you will have to give up usage of index. It's quite long to describe; it still uses GROUP_CONCAT and SUBSTRING_INDEX, but only for the purpose of creating a derived table with only relevant keys. This derived table is then joined with original table. See an example in SQL: selecting top N records per group, another solution.

Best Answer

Related Solutions

Sql-server – how to calculate difference of first row and last row fields value in each group

Mysql – Is it possible to reduce a group to a row based on some criteria

Related Question