Regrouping All Results In A Select With A While

I'm doing some request to help a game dev to balance his game, i'm trying to see how many player use what rune, and at what average level here is my code : declare @runeID varchar(

Solution 1:

I think you want conditional aggregation:

selectfloor(level /10) *10as range_start,
    sum(casewhen i.itemid =22001then1else0end) as use_22001
    avg(casewhen i.itemid =22001then i.maxUpgrade end) as avg_lvl_22001,
    sum(casewhen i.itemid =22002then1else0end) as use_22002
    avg(casewhen i.itemid =22002then i.maxUpgrade end) as avg_lvl_22002
from items i 
innerjoin characters c on i.characterId = c.characterId
where attached >0and i.item_id in (22001, 22002)
groupbyfloor(level /10) *10
sort by range_start ASC

Solution 2:

Here's an attempt to refactor the code. Since 'level' is an integer (from the characters table) there's no need to take the FLOOR. Eliminating that and removing the calculation to a CROSS APPLY'ed virtual table and column 'lvl.lvl'. Then for some reason there's a 'sort by' in the code when it should be ORDER BY. Also, there were some missing commas. Something like this.

select lvl.lvl as range_start,
       sum(casewhen i.itemid = 22001then1else0end) as use_22001, avg(casewhen i.itemid = 22001then i.maxUpgrade end) as avg_lvl_22001,
       sum(casewhen i.itemid = 22002then1else0end) as use_22002, avg(casewhen i.itemid = 22002then i.maxUpgrade end) as avg_lvl_22002,
       avg(i.maxUpgrade) as tot_avg_level,
       count(i.characterId) as tot_num_users
from items i 
     join characters c on i.characterId = c.characterId
     cross apply (select (c.[level]/10)*10 lvl) lvl
where attached > 0groupby lvl.lvl
orderby lvl.lvl;

To build the SQL dynamically so that it creates the 2 columns for each rune (represented by an itemid) in the items table, something like this

declare@select         nvarchar(max)=N'select lvl.lvl as range_start, ',
  @str1           nvarchar(max)=N' sum(case when i.itemid = ',
  @str2           nvarchar(max)=N' then 1 else 0 end) as use_',
  @str3           nvarchar(max)=N', avg(case when i.itemid = ',
  @str4           nvarchar(max)=N' then i.maxUpgrade end) as avg_lvl_',
  @str5           nvarchar(max)=N',',
  @from           nvarchar(max)=N' avg(i.maxUpgrade) as tot_avg_level,
                                   count(i.characterId) as tot_num_users
                            from items i 
                                    join characters c on i.characterId = c.characterId
                                    cross apply (select (c.[level]/10)*10 lvl) lvl
                            where attached > 0
                            group by lvl.lvl
                            order by lvl.lvl;',
  @sql            nvarchar(max);

select@sql=concat(@select,
                   string_agg(concat(@str1, cast(itemid aschar(5)), 
                                     @str2, cast(itemid aschar(5)), 
                                     @str3, cast(itemid aschar(5)), 
                                     @str4, cast(itemid aschar(5)), 
                                     @str5),
                   @from)
from items
where itemid>22000and itemid<24000;

exec sp_executesql @sql;