title: SQL query unique values based on 2 colums
body: <p>I need help figuring this out. </p>
  
  <p>At the moment I have this query: </p>
  
  <pre><code>SELECT Table1.Column1 AS Company
      ,SUM(DATEDIFF(Day, Table2.Column2, Table1.Column3)) AS DiffDate
  FROM Table1
  INNER JOIN Table2 ON Table1.Column6 = Table2.Column6
      AND Table1.Column5 = Table2.Column5
  GROUP BY Company
  </code></pre>
  
  <p>The result of this is...</p>
  
  <pre><code>Company        DiffDate  
  
  company1          8
  </code></pre>
  
  <p>But this is not what I want because there are values that are the combinations of column5 and column6 that are the same. See below. </p>
  
  <pre><code>Company  |  Column6  |  Column5  |  DiffDate  
  Company1 |   5782    |     10    |      2  
  Company1 |   5782    |     10    |      2  
  Company1 |   5782    |     20    |      2  
  Company1 |   5782    |     30    |      2 
  </code></pre>
  
  <p>So the result I'm after is 6, not 8.</p>
  
  <p>I tried using <code>SELECT DISTINCT</code> but that doesn't do anything.</p>
  
  <p>Thank you in advance.  </p>
  
  <p><em>edit 2016-02-09</em> </p>
  
  <p>I've made the following query in SSMS. </p>
  
  <pre><code>;WITH cte AS (SELECT DISTINCT bestlevdat, bestradnr, bestnr FROM dbo.bpa)
  SELECT  dbo.bp.ftgnr AS Företagsnr,SUM(DATEDIFF(Day,cte.bestlevdat, dbo.bp.bestberlevdat)) AS Diff_Bekräftat_dat, 
          SUM(DATEDIFF(Day,cte.bestlevdat, dbo.bp.bestbeglevdat)) AS Diff_Önskat_dat, COUNT(dbo.bp.bestradnr) AS AntalRader, SUM(CASE WHEN datediff(day, cte.bestlevdat, dbo.bp.bestberlevdat) &lt; - 0 THEN 1 ELSE 0 END) AS Antal_avvikande_rader, ROUND((COUNT(dbo.bp.bestradnr) - SUM(CASE WHEN datediff(day, cte.bestlevdat, dbo.bp.bestberlevdat) &lt; - 0 THEN 1.0 ELSE 0.0 END)) * 100 / COUNT(dbo.bp.bestradnr), 1) AS Levsäk
  FROM    dbo.bp INNER JOIN
          cte ON dbo.bp.bestnr = cte.bestnr AND dbo.bp.bestradnr = cte.bestradnr
  WHERE     (YEAR(dbo.bp.bestberlevdat) = '2015')
  GROUP BY dbo.bp.ftgnr
  ORDER BY AntalRader DESC
  </code></pre>
  
  <p>This works a charm. But when I create a new view and paste this query there and try to save I get an error on the ";" again. If I remove ";" in SSMS I get error "The multi-part identifier "cte.bestlevdat" could not be bound" and also converts my query to the following:</p>
  
  <pre><code>WITH cte AS (SELECT DISTINCT bestlevdat, bestradnr, bestnr
                           FROM         dbo.bpa AS bpa_1)
  SELECT     TOP (100) PERCENT dbo.bp.ftgnr AS Företagsnr, SUM(DATEDIFF(Day, cte_1.bestlevdat, dbo.bp.bestberlevdat)) AS Diff_Bekräftat_dat, SUM(DATEDIFF(Day, 
                          cte_1.bestlevdat, dbo.bp.bestbeglevdat)) AS Diff_Önskat_dat, COUNT(dbo.bp.bestradnr) AS AntalRader, SUM(CASE WHEN datediff(day, cte.bestlevdat, 
                          dbo.bp.bestberlevdat) &lt; - 0 THEN 1 ELSE 0 END) AS Antal_avvikande_rader, ROUND((COUNT(dbo.bp.bestradnr) - SUM(CASE WHEN datediff(day, cte.bestlevdat, 
                          dbo.bp.bestberlevdat) &lt; - 0 THEN 1.0 ELSE 0.0 END)) * 100 / COUNT(dbo.bp.bestradnr), 1) AS Levsäk
   FROM         dbo.bp INNER JOIN
                          cte AS cte_1 ON dbo.bp.bestnr = cte_1.bestnr AND dbo.bp.bestradnr = cte_1.bestradnr
   WHERE     (YEAR(dbo.bp.bestberlevdat) = '2015')
   GROUP BY dbo.bp.ftgnr
   ORDER BY AntalRader DESC
  </code></pre>
  
  <p>Sorry if I've missed something obvious, but I'm pretty new with querys and this is my first attempt on "View". </p>