title: Union All Query
body: <p>I have two tables <code>pricelist</code> and <code>stock</code>. Those tables have following columns</p>
  
  <p><code>Pircelist</code> Columns <code>itemno,batchno,mrp,rate</code>. <code>Stock</code> table have <code>itemo,mrp,batchno,npr,stock</code>.</p>
  
  <p>Now i want select <code>itemno,batchno,mrp,rate</code> from <code>pricelist</code> and <code>avg(npr),sum(stock)</code> in <code>stock</code> table. So i tried this</p>
  
  <pre><code>SELECT itemno, 
         salesrate, 
         mrp, 
         batchno, 
         0 AS avgnpr, 
         0 AS stock 
  FROM   pricelist 
  UNION ALL 
  SELECT 0        AS itemno, 
         0        AS salesrate, 
         0        AS mrp, 
         ''       AS batchno, 
         Avg(npr) AS avgnpr, 
         Sum(stock) 
  FROM   stock 
  GROUP  BY itemno, 
            mrp, 
            batchno 
  </code></pre>
  
  <p>Above query retun wrong reslut</p>
  
  <p>But now i am using this: </p>
  
  <pre><code>   Dim Rdr As DataTableReader = Nothing
  
          SELECT itemno, 
                 batchno, 
                 mrp, 
                 rate 
          FROM   pricelist 
  
       Rdr = GlobalAppSetup.Mthds.ExecuteQry(xQry)
  
         While Rdr.Read
  
             Using rdr1 As DataTableReader = GlobalAppSetup.Mthds.ExecuteQry(
             "select avg(npr) as avgnpr,sum(stock) as stock from stock where itemno=" &amp; 
  Rdr.Item("itemno") &amp; " and mrp = " &amp; CDbl(Rdr.Item("mrp")) &amp; " and batchno='" &amp; Trim(Rdr.Item("batchno")) &amp; "' group by itemno,mrp,batchno")
  
                              If rdr1.Read Then
  
                          txt.Text = Rdr.Item("avgnpr")
  
                         End If
                          End Using
  </code></pre>
  
  <p>inside the query with above Query</p>
  
  <p>How can i get <code>itemno,batchno,mrp,rate,avg(npr),sum(stock)</code> those values in single query</p>
  
  <p>Thanks</p>
  
  <p>Am using 
  <strong>Postgresql Version 9.3
  Front End Vb.Net 2008</strong></p>