Creating a sheet from sql requests

I am doing request to get values from a game’s database to help the dev doing balance changing depending on rune using per each 10 levels, and the rune upgrade, so for it, i created a request for each 10 level, and I change the var to the runeID (there are like 60 runes in total so i do that 60 times), and for each result I manually fill a google sheet. I wanted to know if it is possible to directly create all those request in a single request, so i can copy the column and past them all, which will make me win a lot of time, and even if possible make all the runes at same time, so a simple copy-paste and all the data are there

Here is the code I use to take values of a rune each 10 levels, from level 1 to 130, after 130 all are in the same request

declare @runeID varchar(100)
set @runeID=22001

select counT(i.characterid) as 'user level 1 to 10', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>0 and level<11 and attached>0
select counT(i.characterid) as 'user level 11 to 20', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>10 and level<21 and attached>0
select counT(i.characterid) as 'user level 21 to 30', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>20 and level<31 and attached>0
select counT(i.characterid) as 'user level 31 to 40', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>30 and level<41 and attached>0
select counT(i.characterid) as 'user level 41 to 50', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>40 and level<51 and attached>0
select counT(i.characterid) as 'user level 51 to 60', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>50 and level<61 and attached>0
select counT(i.characterid) as 'user level 61 to 70', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>60 and level<71 and attached>0
select counT(i.characterid) as 'user level 71 to 80', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>70 and level<81 and attached>0
select counT(i.characterid) as 'user level 81 to 90', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>80 and level<91 and attached>0
select counT(i.characterid) as 'user level 91 to 100', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>90 and level<101 and attached>0
select counT(i.characterid) as 'user level 101 to 110', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>100 and level<111 and attached>0
select counT(i.characterid) as 'user level 111 to 120', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>110 and level<121 and attached>0
select counT(i.characterid) as 'user level 121 to 130', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>120 and level<131 and attached>0
select counT(i.characterid) as 'user level 131+', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>130 and attached>0

So now this query give me 14 results (and I can only copy them 2 info by 2 info, number of use and average level each 10 levels), so in total 28 info to manually fill just for 1 rune. So again, I’m here to see if it would be possible to at least being able to put all those 28 info in a 2 column table, which from i can copy all 28 info directly, or even a bigger one where i can enter all the rune Ids i an array and it do a giant table with all the runes, 2 column per rune

Answer

Here’s another approach

select lvl.lvl as range_start,
       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;

Advertisement

Answer