title: Sql query on taking the value from a case within select statement in a CTE
body: <p>I am trying to write a SQL. In this, I want columns Bank, and Y-Bank in the output. Y-Bank is being calculated based on certain case statements, using CTE, but I am not able to return Y-Plant as the column.</p>
  <p><img src="https://i.stack.imgur.com/pPjpD.png" alt="This is the sample input and output" /></p>
  <p>I also created a table as shown in the input/output here:<a href="https://dbfiddle.uk/?rdbms=mysql_8.0&amp;fiddle=749e4ca1570880e9c64c4553d18dea1a" rel="nofollow noreferrer">https://dbfiddle.uk/?rdbms=mysql_8.0&amp;fiddle=749e4ca1570880e9c64c4553d18dea1a</a></p>
  <p>Below is the code:</p>
  <pre><code>WITH CTE AS
  (
  SELECT
  &quot;BANK&quot;,
  (select 
  case 
  when &quot;TEST&quot;=0 AND &quot;TEST1&quot;&gt;0
  THEN ( SELECT COUNT(&quot;ZONES&quot;)FROM mytable I WHERE  I.BANK = O.BANK AND I.&quot;ZONES&quot;='Y' )
  END AS &quot;Y-BANK&quot;
  from(
    
   (SELECT  
           
            CASE WHEN ( (SELECT COUNT(&quot;ZONES&quot;)FROM mytable I WHERE I.BINDER = O.BINDER AND I.&quot;ZONES&quot;='N' ) = 0 ) AND ( (SELECT COUNT(&quot;ZONES&quot;)FROM mytable I WHERE I.BINDER = O.BINDER AND I.&quot;ZONES&quot;='Y' ) &gt; 0 )
      THEN ( SELECT COUNT(&quot;ZONES&quot;)FROM mytable I WHERE  I.&quot;TOTAL LINE&quot;= O.&quot;TOTAL LINE&quot;AND I.&quot;ZONES&quot;='N' )
    END AS &quot;TEST&quot;,
            CASE WHEN ( (SELECT COUNT(&quot;ZONES&quot;)FROM mytable I WHERE I.BINDER = O.BINDER AND I.&quot;ZONES&quot;='' ) = 0 ) AND ( (SELECT COUNT(&quot;ZONES&quot;)FROM mytable I WHERE I.BINDER = O.BINDER AND I.&quot;ZONES&quot;='Y' ) &gt; 0 )
         THEN ( SELECT COUNT(&quot;ZONES&quot;)FROM mytable I WHERE  I.&quot;TOTAL LINE&quot; = O.&quot;TOTAL LINE&quot; AND I.&quot;ZONES&quot;='Y' )
    END AS &quot;TEST1&quot;
    from mytable )
    )
    )
  FROM mytable O
  )
   
  
  
  SELECT *
  FROM CTE O
  </code></pre>
  <p>Can someone help me out on how can I correct it?</p>