1

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:

an example how the correct output should look like 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:

example with a sub-query that groups the usernames, no Items.Price selected, though...

4
  • 1
    Please add the results that you get when you run the query you provided. Also, DDL for the underlying tables is never a bother, it can be very useful. Commented Jun 14, 2020 at 21:08
  • @WorldWideDBA, I have pasted the results I get with the query from above, only a fraction of them as I've easily hit the 30.000 symbols mark. Commented Jun 14, 2020 at 21:13
  • 1
    I am afraid you have not, the result set includes the ungrouped item name which your query does not include. Your query returns 4 columns whereas there are 5 in the result set. Commented Jun 14, 2020 at 21:16
  • @WorldWideDBA, good catch, apologies - the only difference is the missing Items.Name added to the SELECT statement - I have included it on the result set so that is becomes clear that the items indeed make the row different and hard to group by "username+name". Commented Jun 14, 2020 at 21:26

1 Answer 1

1

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.

2
  • 1
    The output delivered by your query is 1:1 with the solution of the task, thanks a lot for the help! Actually, to avoid the Items.Price in the GROUP BY, we include it directly in an aggregating function. I thought a sub-query is inevitable but the above way is solving solely by joins :) Commented Jun 15, 2020 at 19:20
  • Glad I could help! Commented Jun 15, 2020 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.