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

MySQL Server

Avatar of undefined
Last Comment
PeterErhard

8/22/2022 - Mon
Tomas Helgi Johannsson

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
ASKER
PeterErhard

Thanks Tomas, would you be able to help be do this please?
ASKER CERTIFIED SOLUTION
Tomas Helgi Johannsson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
PeterErhard

Thank you :)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck