troubleshooting Question

Grouping By/Having Problem in returning columns that I want

Avatar of PeterErhard
PeterErhard asked on
MySQL Server
4 Comments1 Solution294 ViewsLast Modified:
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]
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros