• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

sql count distinct for total column

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.
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
 )

Open in new window

0
Nate_LR
Asked:
Nate_LR
1 Solution
 
lcohanDatabase AnalystCommented:
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
 )
0
 
Scott PletcherSenior DBACommented:

UPDATE wcbt
SET BoxTotal = wcs.kount
FROM dbo.tblWhCoreBoxTotal wcbt
INNER JOIN (
    SELECT f_WellLocationID, AGS_ID, COUNT(*) AS kount
    FROM 
        (SELECT DISTINCT f_WellLocationID, AGS_ID, BOX_NUM, WH_LOCATION, f_SAMPLETYPEID
         FROM dbo.tblWhCoreSample
        ) AS wcs_derived
    GROUP BY f_WellLocationID, AGS_ID
) AS wcs ON wcs.f_WellLocationID = wcbt.f_WellLocID AND
    wcs.AGS_ID = wcbt.AGS_ID

Open in new window

0
 
Nate_LRAuthor Commented:
Thank-you so much!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now