CODEWITHSUNDEEP
sqlsql-servercountpivotaverage
Samy Pereger
Samy Pereger

Reputation: 45

Creating row with different where

I have this code to get the number of users of all items in the list and the average level.

select  itemId,count(c.characterid) as numberOfUse, avg(maxUpgrade) as averageLevel
from items i inner join characters c on i.characterId=c.characterId 
where itemid in (22001,22002,22003,22004,22005,22006,22007,22008,22009,22010,22011,22012,22013,22014,22015,22016,22030,22031,22032,22033,22034,22035,22036,22037,22038,22039,22040,22041,22042,22050,22051,22052,22053,22054,22055,22056,22057,22058,22059,22060,22070,22071,22072,22073,22074,22075,22076,22077,22085,22086,22087,22091,22092) 
and attached>0
group by itemId

It does is creating a row for the rune id, one for the number of users, and one for the average-level people who upgrade it, and it does that for all players of the server.

I would like to create a new column every 10 levels to have stats every 10 levels, so I can see what item is more used depending on player level. The item level depending on the level, so the way I do to select only a certain level is using WHERE itemid>0 and itemid<10, and I do that every 10 levels, copy data, and push them in a google sheet.

So I would like a result with columns :

itemid   use_1-10   avg_level_1-10   use_11-20   avg_level_21-30  etc...

So I could copy all the results at once and not having to do the same process 15 times.

Upvotes: 0

Views: 31

Answers (1)

GMB
GMB

Reputation: 222482

If I am following this correctly, you can do conditional aggregation. Assuming that a "level" is stored in column level in table characters, you would do:

select i.itemId,
    sum(case when c.level between  1 and 10 then 1 else 0 end) as use_1_10,
    avg(case when c.level between  1 and 10 then maxUpgrade end) as avg_level_1_10,
    sum(case when c.level between 11 and 20 then 1 else 0 end) as use_11_20,
    avg(case when c.level between 11 and 20 then maxUpgrade end) as avg_level_11_20,
    ...
from items i 
inner join characters c on i.characterId = c.characterId 
where i.itemid in (...)  and attached > 0
group by i.itemId

Note: consider prefixing column attached in the where clause with the table it belongs to, in order to avoid ambiguity.

Upvotes: 1

Related Questions