title: Mysql conditional MAX or MIN dependent on another column value
body: <p>I want to run a MYSQL query that gets the MAX of one column OR the MIN of the same column, conditional on the value of ANOTHER column. For example, say my table is of:</p>
  
  <p>ID , Gender , Score</p>
  
  <p>Gender could be "boy" or "girl" and I wish to query the aggregated MAX score for girls and the MIN score for boys </p>
  
  <p>I don't want to run complicated JOINS or multiple queries "where ID in...."  as this is already only part of a more sophisticated query with JOIN and I try to avoid performance issues. </p>
  
  <p>I have tried the following:</p>
  
  <pre><code>SELECT  ... list of columns ...                     
      DB_User_ID ,                            
      DB_Gender ,
      IF(DB_Gender ='girl', MAX(DB_Score), 0) AS Max_Girls_Score,
      IF(DB_Gender ='boy', MIN(DB_Score), 0) AS Min_Boys_Score,
      FROM DB
      JOIN .... and continue of query ....
      GROUP BY DB_User_ID 
  </code></pre>
  
  <p>The above query won't work properly because if DB_Gender = 'girl' obviously Min_Boys_Score will be zeroed and vice versa. Question is - is there any logical work around this query to run the MAX condition ONLY for Girls and the MIN only for boys? </p>
  
  <p>Logically, what I'm trying to achieve (see below) is:</p>
  
  <pre><code>SELECT .....
  IF(DB_Gender ='girl', MAX(DB_Score) AS Max_Girls_Score, MIN(DB_Score) AS Min_Boys_Score)
  </code></pre>
  
  <p>But this is not a legit MYSQL query</p>
  
  <p><strong>*********</strong> I MANAGED TO SOLVE THIS, here is a simple solution: <strong>************</strong></p>
  
  <p>The select would have a conditional if:</p>
  
  <pre><code>SELECT {column list}
      IF(DB_Gender ='girl', MAX(DB_Score), MIN(DB_Score)) AS Min_Max_Scores
  ......
  </code></pre>
  
  <p>then added another GROUP BY the conditional field:</p>
  
  <pre><code>GROUP BY DB_User_ID, DB_Gender
  </code></pre>
  
  <p>The outcome are all the rows, grouped by gender, while then Min_MAx_Scores will be either MAX or MIN dependent with the gender</p>
  
  <p><strong>*****</strong> Another solution that I managed to figure out <strong>************</strong></p>
  
  <pre><code>SELECT ......
  SUM(IF(DB_Gender ='girl',MAX(DB_Score),0) AS Max_Girls_Score,
  SUM(IF(DB_Gender = 'boy',MIN(DB_Score),0) AS Min_Boys_Score
  </code></pre>
  
  <p>this will sum the max score for girls and the min score for boys </p>