Mysql – Retrieve game details that their level’s value is higher than 1/10 the sum of guilds levels

MySQL

I've a database which contains tables:

  • Quest:

    PK  Name
PK  qName
    goal

  • Guild:

    PK  Name
PK  gName
    level

  • Game:

    PK  Name
    MaxPlayer
    levels
    uName

  • GuildQuest:

    PK  Name
PK  gName
PK  qName

I need to retrieve the game details that their level's value is higher than 1/10 the sum of guilds levels

This is what I've build so far:

SELECT
  Name, MaxPlayer, levels, uName
  SUM(level) AS guildsLevels
FROM
  Guild
GROUP BY
  level

I need to add the multiplication by 0.10 of the sum which I don't know how to do, and then check every game that its value is bigger than sum of guild levels multiplied by 10%.

I'm using MySQL (and PhpMyAdmin to run the queries).

Best Answer

Assuming the Guild.gName = Game.Name, I think you are looking for something like this:

select
    ga.Name
  , ga.MaxPlayer
  , ga.levels
  , ga.uName
  , gu.GuildsLevels
from Game ga 
  inner join (
    select gName, GuildsLevels=sum(level)
      from Guilds
      group by gName
      ) as gu on ga.Name   = gu.gName 
             and ga.levels>=(gu.guildslevels*0.10)
Related Question