<div>
You need to put a distinct col_name in the count like below but it also needs to have distinct values for sure. If not you could add a relative record number to your subquery and count on that field:<br />
<br />
UPDATE tblWhCoreBoxTotal<br />
SET BoxTotal = <br />
<br />
(SELECT COUNT(distinct f_WellLocationID)<br />
FROM <br />
(SELECT DISTINCT f_WellLocationID, AGS_ID, BOX_NUM, WH_LOCATION, f_SAMPLETYPEID<br />
FROM tblWhCoreSample<br />
WHERE tblWhCoreSample.f_WellLoca<wbr />tionID = tblWhCoreBoxTotal.f_WellLo<wbr />cID<br />
AND tblWhCoreSample.AGS_ID = tblWhCoreBoxTotal.AGS_ID<br />
&nbsp; &nbsp;) t<br />
&nbsp;)<br />

</div>


You need to put a distinct col_name in the count like below but it also needs to have distinct values for sure. If not you could add a relative record number to your subquery and count on that field:

UPDATE tblWhCoreBoxTotal
SET BoxTotal =

(SELECT COUNT(distinct f_WellLocationID)
FROM
(SELECT DISTINCT f_WellLocationID, AGS_ID, BOX_NUM, WH_LOCATION, f_SAMPLETYPEID
FROM tblWhCoreSample
WHERE tblWhCoreSample.f_WellLocationID = tblWhCoreBoxTotal.f_WellLocID
AND tblWhCoreSample.AGS_ID = tblWhCoreBoxTotal.AGS_ID
   ) t
 )


<div>
<div class="content wysiwyg-content">
I'm trying to create a box total column in a table by counting distinct cases from multiple columns in the same table. &nbsp;I can do this properly when the box total column is in another table, but I can't figure out the self-join or whatever is required when it's in the same table.<br />

<pre><code id="code-8229754">UPDATE tblWhCoreBoxTotal
SET BoxTotal = 

(SELECT COUNT(*)
FROM 
(SELECT DISTINCT f_WellLocationID, AGS_ID, BOX_NUM, WH_LOCATION, f_SAMPLETYPEID
FROM tblWhCoreSample
WHERE tblWhCoreSample.f_WellLocationID = tblWhCoreBoxTotal.f_WellLocID
AND tblWhCoreSample.AGS_ID = tblWhCoreBoxTotal.AGS_ID
   ) t
 )</code></pre>
</div>
</div>


I'm trying to create a box total column in a table by counting distinct cases from multiple columns in the same table.  I can do this properly when the box total column is in another table, but I can't figure out the self-join or whatever is required when it's in the same table.

sql count distinct for total column

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.