Link to home
Start Free TrialLog in
Avatar of PeterErhard
PeterErhard

asked on

Grouping By/Having Problem in returning columns that I want

I have the following query and I'm having problems getting it to return the data that I want.

I need to "SELECT" all those records, but the "HAVING" clause should just use a "GROUP"ing of "PageName,PlayerName".  So at the moment, it isn't returning any results because it isn't a true SUM of all records for SumBatRuns.

How could I monitor the below query to do that?

SELECT Player.PageName, 
       Player.PlayerName, 
       MatchPlayerBatting.BatRuns, 
       MatchPlayerBatting.BatBalls, 
       MatchPlayerBatting.BatMinutes, 
       MatchPlayerBatting.BatFours, 
       MatchPlayerBatting.BatSixes, 
       MatchPlayerBatting.BatHowOut, 
       MatchPlayerBatting.InningsNumber, 
       MatchPlayerResult.PlayingFor, 
       MatchPlayerResult.PlayingAgainst, 
       MatchPlayerResult.MatchVenue, 
       MatchPlayerResult.MatchDate, 
[b]       SUM(MatchPlayerBatting.BatRuns)            AS SumBatRuns, [/b]
       SUM(MatchPlayerBatting.BatMinutes)         AS SumBatMinutes, 
       COUNT(CASE 
               WHEN MatchPlayerBatting.BatHowOut <> '0' 
                    AND MatchPlayerBatting.BatHowOut <> 'absent hurt' THEN 
               MatchPlayerBatting.PlayerCommonName 
             end)                                 AS Innings, 
       COUNT(DISTINCT MatchPlayerBatting.MatchID) AS Matches 
FROM   MatchPlayerResult 
       INNER JOIN MatchPlayerBatting 
               ON MatchPlayerResult.MatchID = MatchPlayerBatting.MatchID 
                  AND MatchPlayerResult.MatchType = MatchPlayerBatting.MatchType 
                  AND MatchPlayerResult.PlayerID = 
                      MatchPlayerBatting.PlayerCommonName 
       INNER JOIN Player 
               ON MatchPlayerBatting.PlayerCommonName = Player.PlayerId 
                  AND MatchPlayerBatting.MatchID IN ( 275, 728, 729, 730, 
                                                      733, 735, 736, 796, 
                                                      798, 887, 889, 891, 
                                                      922, 924, 926, 1029, 
                                                      1030, 1031, 1035, 1038, 
                                                      1040, 1082, 1084, 1087, 
                                                      1129, 1141, 1215, 1216, 
                                                      1217, 1235, 1236, 1238, 
                                                      1383, 1386, 1388, 1488, 
                                                      1491, 1493, 1565, 1571, 
                                                      1573, 1721, 1723, 1744, 
                                                      1742, 1739, 1894, 1896, 
                                                      1955, 1957, 2021, 2020 ) 
                  AND MatchPlayerBatting.MatchType = 'Test' 
                  AND ( TeamOne IN ( 'New Zealand' ) 
                         OR TeamTwo IN ( 'New Zealand' ) 
                         OR TeamThree IN ( 'New Zealand' ) ) 
WHERE  ( BatHowOut <> '0' ) 
[b]GROUP  BY PageName, 
          PlayerName, 
          BatRuns, 
          BatBalls, 
          BatMinutes, 
          BatFours, 
          BatSixes, 
          BatHowOut, 
          InningsNumber, 
          PlayingFor, 
          PlayingAgainst, 
          MatchVenue, 
          MatchDate 
HAVING ( SumBatRuns >= 200 ) [/b]

Open in new window

Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Hi!

You will need to have the query split into two subqueries, one with a "simple list" of all selected columns and the other a grouping of just playername, pagename with all summable columns you want and then join these subqueries together on playername,pagename.

Regards,
     Tomas Helgi
Avatar of PeterErhard
PeterErhard

ASKER

Thanks Tomas, would you be able to help be do this please?
ASKER CERTIFIED SOLUTION
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you :)