title: Error: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists
body: <p>I am trying to create a stored procedure that will email out an HTML table that uses a query with Unions in it. But I keep getting:</p>
  
  <p>"All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."</p>
  
  <p>I went through and I believe all my selects for the Unions have the same number for columns. Any one have any ideas? I am using SQL 2012.</p>
  
  <pre><code>DECLARE @DailySales varchar(max)
  
  With Daily_Sales_CTE as (
  SELECT 
  TD = 'Base','',
  TD = CONVERT(int,SUM(totalprice)/1000),'', 
  TD = CONVERT(int,SUM(totalcost)/1000),'',
  TD = CONVERT(int,SUM(totalprice-totalcost)/1000),'',
  TD = CONVERT(decimal (3,3),1-((SUM(totalcost))/(SUM(totalprice)))),'',
  TD = CONVERT(int, SUM(totweight)/2000)
  FROM ubs_saleslookup_cost A
  WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) =          DATEPART(YYYY, GETDATE()) and DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
  a.cust&lt;&gt;'1522292' and a.cust&lt;&gt;'1435230' and a.location&lt;&gt;'g-ds'
  union
  Select
  TD = 'Direct','',
  TD = CONVERT(int,SUM(totalprice)/1000),'', 
  TD = CONVERT(int,SUM(totalcost)/1000),'',
  TD = CONVERT(int,SUM(totalprice-totalcost)/1000),'',
  TD = CONVERT(decimal (3,3),1-((SUM(totalcost))/(SUM(totalprice)))),'',
  TD = CONVERT(int, SUM(totweight)/2000)
  FROM ubs_saleslookup_cost A
  WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE()) and DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
  a.cust&lt;&gt;'1522292' and a.cust&lt;&gt;'1435230' and a.location='g-ds'
  union
  SELECT 
  TD = 'Acero',
  TD = CONVERT(int,SUM(totalprice)/b.xchgrate/1000),'', 
  TD = CONVERT(int,SUM(totalcost)/b.xchgrate/1000),'',
  TD = CONVERT(int,SUM(totalprice-totalcost)/b.xchgrate/1000),'',
  TD = CONVERT(decimal (3,3),1-((SUM(totalcost)/b.xchgrate)/(SUM(totalprice)/b.xchgrate))),'',
  TD = CONVERT(int, SUM(totweight)/2000)
  FROM acero.dbo.ubs_saleslookup_cost A
  left join acero.dbo.acero_rate b on DATEPART(YYYY,A.date)=b.year1 and DATEPART(MM,A.date)=b.periodid
  WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE())  and   DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
  a.cust&lt;&gt;'1512098'
  group by b.xchgrate
  union
  SELECT
  TD = 'Chile','',
  TD = CONVERT(int,SUM(totalprice)/b.xchgrate/1000),'', 
  TD = CONVERT(int,SUM(totalcost)/b.xchgrate/1000),'',
  TD = CONVERT(int,SUM(totalprice-totalcost)/b.xchgrate/1000),'',
  TD = CONVERT(decimal (3,3),1-((SUM(totalcost)/b.xchgrate)/(SUM(totalprice)/b.xchgrate))),'',
  TD = CONVERT(int, SUM(totweight)/2000)
  FROM chile.dbo.ubs_saleslookup_cost A
  left join chile.dbo.chile_rate b on DATEPART(YYYY,A.date)=b.year1 and DATEPART(MM,A.date)=b.periodid
  WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE())  and   DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
  a.cust&lt;&gt;'1512098'
  group by b.xchgrate
  union
  SELECT 
  TD = 'Total','',
  TD = CONVERT(int,SUM(a.totalprice+b.totalprice+isnull(c.totalprice,0))/1000),'', 
  TD = CONVERT(int,SUM(a.totalcost+b.totalcost+isnull(c.totalprice,0))/1000),'',
  TD = CONVERT(int,SUM(a.totalprice-a.totalcost+b.totalprice-b.totalcost+isnull(c.totalprice-c.totalcost,0))/1000),'',
  TD = CONVERT(decimal (3,3),1-((SUM(a.totalcost+b.totalcost+isnull(c.totalcost,0)))/(SUM(a.totalprice+b.totalprice+isnull(c.totalprice,0))))),'',
  TD = CONVERT(int, SUM(a.totweight+b.totweight+isnull(c.totweight,0)))
  FROM (select 'CurMonth' as Month, sum(totalprice) as totalprice, sum(totalcost) as totalcost, sum(totweight)/2000 as totweight
  from ubs_saleslookup_cost 
  WHERE DATEPART(MM,date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,date) = DATEPART(YYYY, GETDATE()) and   DATEPART(DD,date) = DATEPART(DD, GETDATE()) and
  cust&lt;&gt;'1522292' and cust&lt;&gt;'1435230') a
  left join (select 'CurMonth' as Month, sum(a.totalprice)/b.xchgrate as totalprice, sum(a.totalcost)/b.xchgrate as totalcost, 
  sum(a.totweight)/2000 as totweight
  from acero.dbo.ubs_saleslookup_cost A
  left join acero.dbo.acero_rate b on DATEPART(YYYY,A.date)=b.year1 and DATEPART(MM,A.date)=b.periodid
  WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE())  and   DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
  a.cust&lt;&gt;'1512098' group by b.xchgrate) b on a.month=b.month
  left join (select 'CurMonth' as Month, sum(a.totalprice)/b.xchgrate as totalprice, sum(a.totalcost)/b.xchgrate as totalcost, 
  sum(a.totweight)/2000 as totweight
  from chile.dbo.ubs_saleslookup_cost A
  left join chile.dbo.chile_rate b on DATEPART(YYYY,A.date)=b.year1 and DATEPART(MM,A.date)=b.periodid
  WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE())  and   DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
  a.cust&lt;&gt;'1512098' group by b.xchgrate) c on a.month=c.month
  
  )
  
  Select @DailySales = 
  N'&lt;style type="text/css"&gt;
  #box-table
  {
  font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
  font-size: 12px;
  text-align: center;
  border-collapse: collapse;
  border-top: 7px solid #9baff1;
  border-bottom: 7px solid #9baff1;
  }
  #box-table th
  {
  font-size: 13px;
  font-weight: Bold;
  background: #b9c9fe;
  border-right: 2px solid #9baff1;
  border-left: 2px solid #9baff1;
  border-bottom: 2px solid #9baff1;
  color: #039;
  }
  #box-table td
  {
  border-right: 1px solid #aabcfe;
  border-left: 1px solid #aabcfe;
  border-bottom: 1px solid #aabcfe;
  color: #669;
  }
  tr:nth-child(odd)    { background-color:#eee; }
  tr:nth-child(even)   { background-color:#fff; } 
  &lt;/style&gt;'+  
  N'&lt;H1&gt;&lt;font color="Black"&gt;MTD Results&lt;/H1&gt;'+
  N'&lt;table id="box-table"&gt;'+
  N'&lt;tr&gt;&lt;font color = "Black"&gt;
  &lt;th&gt; Type &lt;/th&gt;
  &lt;th&gt; Sales &lt;/th&gt;
  &lt;th&gt; Cost &lt;/th&gt;
  &lt;th&gt; Margin &lt;/th&gt;
  &lt;th&gt; Percentage &lt;/th&gt;
  &lt;th&gt; Tons &lt;/th&gt;
  &lt;/tr&gt;'
  + CAST ( ( select * from Daily_Sales_CTE
  
  FOR XML PATH ('tr')
  ) as varchar(max))
  + '&lt;/table&gt;'
  
  EXEC msdb.dbo.sp_send_dbmail 
  @recipients = 'Email@domain.com',
  @copy_recipients = '', 
  @subject = 'Daily Sales',
  @body = @DailySales,
  @body_format = 'html'
  </code></pre>