Count Distinct –Alternate Solution in T-SQL


http://social.msdn.microsoft.com/Forums/en-US/53b7d5af-875c-4399-8593-299f6c230ada/update-with-count-distinct?forum=transactsql

CREATE TABLE mytable(
  mukey nvarchar (30) NULL,
  cokey nvarchar (30) NULL,
  ncomp int NULL);
INSERT INTO mytable(mukey, cokey, ncomp)
VALUES 
  ('1017271', '9302647', NULL),
  ('1017271', '9302647', NULL),
  ('1017271', '9302647', NULL),
  ('1017271', '9302648', NULL),
  ('1017271', '9302648', NULL),
  ('1017271', '9302648', NULL),
  ('1017271', '9302648', NULL)


;with PartialSums as (
SELECT mukey, cokey,COUNT(*) AS countstarpartialcount FROM mytable  
GROUP BY mukey, cokey
)
,mycte as (SELECT mukey,
SUM(countstarpartialcount) AS countstar,
SUM(1) AS countproductkeys FROM partialsums 
Group by mukey)


Merge mytable tgt 
using mycte  as src on tgt.mukey=src.mukey
When matched Then 
Update 
set ncomp=src.countproductkeys;

select * from mytable


drop table mytable

 

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2008/09/22/distinct-aggregation-considered-harmful.aspx

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s