CODEWITHSUNDEEP
mysqlsql
user1970743
user1970743

Reputation: 11

Max with Having clause

I have this database

Game(ID,Version,Name,Price,Color,IDDISTRIBUTION,#worker)
Distribution(ID,Name,#worker,quality)  
Istitute(ID,Name,NINCeo,City)  
Sponsor(IDGAME,IDISTITUTE,VERSIONGAME)  
Designer(NIN,name,surname,role,budget)
Project(NINDESIGNER,IDGAME,VERSIONGAME,#hours)

(the uppercase is for indicate the foreign keys)

I have to write in SQL this nested query:

(In the parenthesis are the row to select,# is the result of the COUNT query)

Upvotes: 1

Views: 71

Answers (3)

barker
barker

Reputation: 1055

Check out window functions! They allow you to do fun analytics, such as who had the 3rd most number of games? Try this:

with counts as (
    select
    i.id
    ,i.name
    ,count(distinct g.id) as gamecount
    from istitute i
    inner join games g
    on i.id=g.id
    and i.name=g.name
    group by i.id
    ,i.name
)

select
c.id
,c.name
,RANK() over (ORDER BY c.gamecount DESC) as rank
from counts c

Upvotes: 1

Adikari Nadeesha
Adikari Nadeesha

Reputation: 312

a) For Select the Istitute that sponsored the max number of games 

;with TempCount as (
     select  IDISTITUTE,
            count(IDGAME) As GameCount 
     from Sponsor S 
     Group by  IDGAME,IDISTITUTE
    )

    select Top  1
    T.IDISTITUTE,
    Count(T.GameCount) As  MaxGameCount
    from TempCount T 
    Group  by T.IDISTITUTE order  by Count(T.GameCount) desc

b) For Select the Istitute that sponsored the min number of games

 ;with TempCount as (
     select  IDISTITUTE,
            count(IDGAME) As GameCount 
     from Sponsor S 
     Group by  IDGAME,IDISTITUTE
    )

    select Top  1
    T.IDISTITUTE,
    Count(T.GameCount) As  MinGameCount
    from TempCount T 
    Group  by T.IDISTITUTE order  by Count(T.GameCount) asc

Upvotes: 1

user1970743
user1970743

Reputation: 11

I think that i've found the solution: 

a)SELECT Name,#max_game
  FROM Istitute
  WHERE(SELECT COUNT (IDGAME) as #max_game
  FROM Sponsor,Game,Istitute
  WHERE(IDGAME=Game.id AND IDISTITUTE=Istitute.id AND VERSIONGAME=Game.Version))
  HAVING=(SELECT MAX COUNT (IDGAME) as #realmax_game)
  FROM Sponsor,Game,Istitute
  WHERE(IDGAME=Game.id AND IDISTITUTE=Istitute.id AND VERSIONGAME=Game.Version))

b) the same as a) but with MIN

c)SELECT Name,#game
  FROM Istitute
  WHERE(SELECT COUNT (IDGAME) as #game
  FROM Sponsor,Game,Istitute
  WHERE(IDGAME=Game.id AND IDISTITUTE=Istitute.id AND VERSIONGAME=Game.Version))
  HAVING #game<=10

Upvotes: 0

Related Questions