CODEWITHSUNDEEP
javasql
Duane Allman
Duane Allman

Reputation: 571

SQL COUNT display more than just the count?

I have a database of lots of cricket bowls. Schema is: 

Player(PlayerID, Initials, Surname)
 - Stores all the players
Bowl(BowlID, Striker, Non-Striker, Bowler, Runs)
 - Stores info for every ball
Team(TeamID, name)
 - Stores all the teams
SquadPlayer(TeamID, PlayerID, MatchID)
 - Stores a record of who was playing at each match and for which team

I'm trying to find the number of sixes scored by each player in the England team. (I'm using openoffice style HSQL)

SELECT "Player"."Initials", "Player"."Surname", COUNT ("Bowl"."Striker") AS "No. Sixes"
FROM "Bowl", "Player", "Player" AS "Bowler"
WHERE "Bowl"."Striker" = "Player"."PlayerID"
AND "Bowl"."Bowler" = "Bowler"."PlayerID"
AND "Bowl"."Striker" IN (
    SELECT DISTINCT "Player"."PlayerID" 
    FROM "Player", "Team", "SquadPlayer"
    WHERE "Player"."PlayerID" = "SquadPlayer"."PlayerID" 
    AND "Team"."TeamID" = "SquadPlayer"."TeamID" 
    AND "Team"."Name" = 'England' )
AND "Bowl"."Runs" = '6'
GROUP BY "Bowl"."Striker"

This is the SQL I'm trying to run, but it won't work. If I omit the first two columns in the SELECT part (Player.Initials and Player.Surname) it works fine, but it's not very useful as I can't work out WHO scores all the sixes.

I'm running it all through java, I get the exception: 

java.sql.SQLException: Not in aggregate function or group by clause

So why doesn't this work? And how do I display the names alongside it?

Upvotes: 1

Views: 148

Answers (4)

Habibillah
Habibillah

Reputation: 28695

If you use count, sum, etc (aggregate function) in your query, all column you retrieve that not in aggregating must be in group by clause.

change your group by clause to become

GROUP BY  "Player"."Initials", "Player"."Surname"

Upvotes: 0

Tobb
Tobb

Reputation: 12205

When you have a count (or any aggregate function) in your select clause, all un-aggregated columns in the select-clause must also appear in the group-by clause.

Make your group by-clause look like this:

GROUP BY "Bowl"."Striker", "Player"."Initials", "Player"."Surname"

and it should work..

Upvotes: 1

nikolifish
nikolifish

Reputation: 512

Your group by should be the columns your not aggregating.

SELECT "Player"."Initials", "Player"."Surname", COUNT ("Bowl"."Striker") AS "No. Sixes"
FROM "Bowl", "Player", "Player" AS "Bowler"
WHERE "Bowl"."Striker" = "Player"."PlayerID"
AND "Bowl"."Bowler" = "Bowler"."PlayerID"
AND "Bowl"."Striker" IN (
    SELECT DISTINCT "Player"."PlayerID" 
    FROM "Player", "Team", "SquadPlayer"
    WHERE "Player"."PlayerID" = "SquadPlayer"."PlayerID" 
    AND "Team"."TeamID" = "SquadPlayer"."TeamID" 
    AND "Team"."Name" = 'England' )
AND "Bowl"."Runs" = '6'
GROUP BY "Player"."Initials", "Player"."Surname"

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79959

Any field in the SELECT clause needs to be either with an aggregate function or included in the GROUP BY clause thats why you are getting this error. So you have to include the "Player"."Initials", "Player"."Surname" in the GROUP BY clause or use an aggregate function with them. Something like:

SELECT "Player"."Initials", "Player"."Surname", 
      COUNT ("Bowl"."Striker") AS "No. Sixes"
FROM "Bowl", "Player", "Player" AS "Bowler"
WHERE "Bowl"."Striker" = "Player"."PlayerID"
AND "Bowl"."Bowler" = "Bowler"."PlayerID"
AND "Bowl"."Striker" IN (
    SELECT DISTINCT "Player"."PlayerID" 
    FROM "Player", "Team", "SquadPlayer"
    WHERE "Player"."PlayerID" = "SquadPlayer"."PlayerID" 
    AND "Team"."TeamID" = "SquadPlayer"."TeamID" 
    AND "Team"."Name" = 'England' )
AND "Bowl"."Runs" = '6'
GROUP BY "Bowl"."Striker", "Player"."Initials", "Player"."Surname"

Upvotes: 0

Related Questions