title: select * from (select count(*) from table)
body: <p>table : instalation</p>
  
  <pre><code>+ id    + id_city   +   name    + sts   +
  -------------------------------------------
  + 1     +   1       +   jerry   +   1   +
  + 2     +   1       +   david   +   0   +
  + 3     +   2       +   will    +   1   +
  + 4     +   3       +   nanda   +   2   +
  + 5     +   3       +   maya    +   0   +
  + 6     +   4       +   maya    +   2   +
  </code></pre>
  
  <p>instractions from sts column</p>
  
  <pre><code>0 --&gt; pending
  1 --&gt; success
  2 --&gt; fail
  </code></pre>
  
  <p>table : city</p>
  
  <pre><code>+ id_city   +   nm_city     +
  -----------------------------
  +   1       +   california  +
  +   2       +   alaska      +
  +   3       +   colorado    +
  +   4       +   georgia     +
  +   5       +   hawaii      +
  +   6       +   lowa        +
  </code></pre>
  
  <p>I want to add rows from table installation combined  with city table,the following table desired result</p>
  
  <pre><code>+   nm_city     + id_city   +   suc + fail  +   pending +
  ----------------------------------------------------------
  +   california  +   1       +   2   +   0   +   1
  +   alaska      +   2       +   1   +   0   +   0
  +   colorado    +   3       +   0   +   1   +   1
  +   georgia     +   4       +   0   +   1   +   0
  </code></pre>
  
  <p>I have tried using the following query but the sum for SUC column,FAIL and PENDING does not fit</p>
  
  <pre><code>select * from
  (select w.nm_city,i.id_city from installation i join city w on i.id_city=w.id_city group by i.id_city) WIL,
  (select count(*) as suc from installation where sts=1) SUC,
  (select count(*) as fail from installation where sts=2) FAIL,
  (select count(*) as pen from installation where sts=0) PEN
  </code></pre>
  
  <p>please help me</p>