title: sql sum distinct with same values on cells
body: <p>I have this 2 tables</p>
  
  <p><strong>[Table1]</strong></p>
  
  <pre><code>Name     |    cr      |   value   |   obs 
  ==============================================
  John          1           300       
  John          10          550
  John          12          34       
  John          401         190        
  John          10          300       
  </code></pre>
  
  <p><strong>[Table2]</strong></p>
  
  <pre><code>Name     |    cr      |   value   |   obs 
  ==============================================
  John          500         2         obs1
  John          500         2         value2
  John          500         170        
  </code></pre>
  
  <p>And i need to have this</p>
  
  <pre><code>name  |  cr 1   |  cr 10    |   cr 12   |  cd 500  |  cr 401  | total
  John     300        850          34          174        190     1548
  </code></pre>
  
  <p>I'm trying to use this query but not getting the right result</p>
  
  <pre><code>select 
  nome,
  isnull(sum(distinct(case when cr = 1 then value end)),0) as 'cr 1',
  isnull(sum(distinct(case when cr = 10 then value end)),0) as 'cr 10',
  isnull(sum(distinct(case when cr = 12 then value end)),0) as 'cr 12',
  isnull(sum(distinct(case when cr = 500 then value end)),0) as 'cr 500',
  isnull(sum(distinct(case when cr = 401 then value end)),0) as 'cr 401',
  (   isnull(sum(distinct(case when cr = 1 then value end)),0) +
      isnull(sum(distinct(case when cr = 10 then value end)),0) +
      isnull(sum(distinct(case when cr = 12 then value end)),0) +
      isnull(sum(distinct(case when cr = 500 then value end)),0) +
      isnull(sum(distinct(case when cr = 401 then value end)),0)
  ) AS Total
  
  from table1 left join table2 on table1.name = table2.name
  
  group by name
  </code></pre>
  
  <p>The problem is that with this query I do not have the right result on the 'cr 500' </p>
  
  <p>Any help will be welcome! </p>
  
  <p>Thanks in advance! </p>