Mfcttrf

Why is one of Madera Municipal's runways labelled with only "R" on both sides?

How can electric fields be used to detect cracks in metals?

Do simulator games use a realistic trajectory to get into orbit?

What is the reason for double NULL check of pointer for mutex lock

Impedance ratio vs. SWR

Project Euler #7 10001st prime in C++

Soft question: Examples where lack of mathematical rigour cause security breaches?

Logarithm of exponential

At what point in time did Dumbledore ask Snape for this favor?

How to construct an hbox with negative height?

eval() in Lightning Web Components

Trapping Rain Water

Grover algorithm for a database search: where is the quantum advantage?

When conversion from Integer to Single may lose precision

Generate a Graeco-Latin square

What makes an item an artifact?

Should I avoid hard-packed crusher dust trails with my hybrid?

Using "subway" as name for London Underground?

What do abbreviations in movie scripts stand for?

Pre-1972 sci-fi short story or novel: alien(?) tunnel where people try new moves and get destroyed if they're not the correct ones

Compiling c files on ubuntu and using the executable on Windows

Does an ice chest packed full of frozen food need ice?

Thread Pool C++ Implementation

What can I, as a user, do about offensive reviews in App Store?

SQL counting distinct over partition

Distinct Combination of Two ColumnsSelecting Distinct Rows but Counting All RowsHive. Over partition by vs where conditionsCounting unique (distinct) users per dayHow to apply a condition on “count over partition”?Select last non-null value for given partition - Postgres 10Counting and grouping over multiple OUTER JOINsselect distinct values from foreign key constarintHaving issue with window function COUNT counting duplicatesUsing Window Function instead of Select sub-query for counting

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

3

I have a table with two columns, I want to count the distinct values on Col_B over (conditioned by) Col_A.

MyTable

Col_A | Col_B 
A | 1
A | 1
A | 2
A | 2
A | 2
A | 3
b | 4
b | 4
b | 5

Expected Result

Col_A | Col_B | Result
A | 1 | 3
A | 1 | 3
A | 2 | 3
A | 2 | 3
A | 2 | 3
A | 3 | 3
b | 4 | 2
b | 4 | 2
b | 5 | 2

I tried the following code

select *, 
count (distinct col_B) over (partition by col_A) as 'Result'
from MyTable

count (distinct col_B) is not working.
How can I rewrite the count function to count distinct values?

sql-server count window-functions

share|improve this question

edited 4 hours ago

Paul White♦

55.6k14293465

asked 8 hours ago

sara92sara92

162

New contributor

sara92 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Dense Rank can be used here possibly
  
  – scsimon
  7 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  For future reference, could you please supply table structures as DDL (CREATE TABLE blah (...);) and table data as DML (INSERT INTO blah VALUES (...);`) - it makes life much easier for everyone - help us to help you! p.s. welcome to the forum! :-)
  
  – Vérace
  6 hours ago
  

  
  
  
  

add a comment | 

3

I have a table with two columns, I want to count the distinct values on Col_B over (conditioned by) Col_A.

MyTable

Col_A | Col_B 
A | 1
A | 1
A | 2
A | 2
A | 2
A | 3
b | 4
b | 4
b | 5

Expected Result

Col_A | Col_B | Result
A | 1 | 3
A | 1 | 3
A | 2 | 3
A | 2 | 3
A | 2 | 3
A | 3 | 3
b | 4 | 2
b | 4 | 2
b | 5 | 2

I tried the following code

select *, 
count (distinct col_B) over (partition by col_A) as 'Result'
from MyTable

count (distinct col_B) is not working.
How can I rewrite the count function to count distinct values?

sql-server count window-functions

share|improve this question

edited 4 hours ago

Paul White♦

55.6k14293465

asked 8 hours ago

sara92sara92

162

New contributor

sara92 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Dense Rank can be used here possibly
  
  – scsimon
  7 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  For future reference, could you please supply table structures as DDL (CREATE TABLE blah (...);) and table data as DML (INSERT INTO blah VALUES (...);`) - it makes life much easier for everyone - help us to help you! p.s. welcome to the forum! :-)
  
  – Vérace
  6 hours ago
  

  
  
  
  

add a comment | 

I have a table with two columns, I want to count the distinct values on Col_B over (conditioned by) Col_A.

MyTable

Col_A | Col_B 
A | 1
A | 1
A | 2
A | 2
A | 2
A | 3
b | 4
b | 4
b | 5

Expected Result

Col_A | Col_B | Result
A | 1 | 3
A | 1 | 3
A | 2 | 3
A | 2 | 3
A | 2 | 3
A | 3 | 3
b | 4 | 2
b | 4 | 2
b | 5 | 2

I tried the following code

select *, 
count (distinct col_B) over (partition by col_A) as 'Result'
from MyTable

count (distinct col_B) is not working.
How can I rewrite the count function to count distinct values?

sql-server count window-functions

share|improve this question

edited 4 hours ago

Paul White♦

55.6k14293465

asked 8 hours ago

sara92sara92

162

New contributor

sara92 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

Col_A | Col_B 
A | 1
A | 1
A | 2
A | 2
A | 2
A | 3
b | 4
b | 4
b | 5

Col_A | Col_B | Result
A | 1 | 3
A | 1 | 3
A | 2 | 3
A | 2 | 3
A | 2 | 3
A | 3 | 3
b | 4 | 2
b | 4 | 2
b | 5 | 2

select *, 
count (distinct col_B) over (partition by col_A) as 'Result'
from MyTable

share|improve this question

edited 4 hours ago

Paul White♦

55.6k14293465

asked 8 hours ago

sara92sara92

162

New contributor

sara92 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 4 hours ago

Paul White♦

55.6k14293465

asked 8 hours ago

sara92sara92

162

New contributor

sara92 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited 4 hours ago

Paul White♦

55.6k14293465

edited 4 hours ago

Paul White♦

55.6k14293465

asked 8 hours ago

sara92sara92

162

New contributor

sara92 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 8 hours ago

sara92sara92

162

3 Answers
3

active

oldest

votes

5

This is how I'd do it:

SELECT *
FROM #MyTable AS mt
CROSS APPLY ( SELECT COUNT(DISTINCT mt2.Col_B) AS dc
 FROM #MyTable AS mt2
 WHERE mt2.Col_A = mt.Col_A
 -- GROUP BY mt2.Col_A 
 ) AS ca;

The GROUP BY clause is redundant, but may give you a better execution plan.

Consider voting for OVER clause enhancement request - DISTINCT clause for aggregate functions on the feedback site if you would like this added natively to SQL Server.

share|improve this answer

edited 4 hours ago

Paul White♦

55.6k14293465

answered 6 hours ago

Erik DarlingErik Darling

23.9k1375121

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Wow, I had no idea they've been looking into this since 2008 :-)
  
  – Lennart
  3 hours ago
  

  
  
  
  

add a comment | 

1

create table #MyTable (
Col_A varchar(5),
Col_B int
)

insert into #MyTable values ('A',1)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',3)

insert into #MyTable values ('B',4)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',5)


;with t1 as (

select t.Col_A,
 count(*) cnt
 from (
 select Col_A,
 Col_B,
 count(*) as ct
 from #MyTable
 group by Col_A,
 Col_B
 ) t
 group by t.Col_A
 )

select a.*,
 t1.cnt
 from #myTable a
 join t1
 on a.Col_A = t1.Col_a

share|improve this answer

answered 7 hours ago

kevinnwhatkevinnwhat

68018

add a comment | 

1

You can emulate it by using dense_rank, and then pick the maximum rank for each partition:

select col_a, col_b, max(rnk) over (partition by col_a)
from (
 select col_a, col_b
 , dense_rank() over (partition by col_A order by col_b) as rnk 
 from #mytable
) as t 

share|improve this answer

answered 3 hours ago

LennartLennart

13.3k21343

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

sara92 is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f239788%2fsql-counting-distinct-over-partition%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

5

This is how I'd do it:

SELECT *
FROM #MyTable AS mt
CROSS APPLY ( SELECT COUNT(DISTINCT mt2.Col_B) AS dc
 FROM #MyTable AS mt2
 WHERE mt2.Col_A = mt.Col_A
 -- GROUP BY mt2.Col_A 
 ) AS ca;

The GROUP BY clause is redundant, but may give you a better execution plan.

Consider voting for OVER clause enhancement request - DISTINCT clause for aggregate functions on the feedback site if you would like this added natively to SQL Server.

share|improve this answer

edited 4 hours ago

Paul White♦

55.6k14293465

answered 6 hours ago

Erik DarlingErik Darling

23.9k1375121

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Wow, I had no idea they've been looking into this since 2008 :-)
  
  – Lennart
  3 hours ago
  

  
  
  
  

add a comment | 

5

This is how I'd do it:

SELECT *
FROM #MyTable AS mt
CROSS APPLY ( SELECT COUNT(DISTINCT mt2.Col_B) AS dc
 FROM #MyTable AS mt2
 WHERE mt2.Col_A = mt.Col_A
 -- GROUP BY mt2.Col_A 
 ) AS ca;

The GROUP BY clause is redundant, but may give you a better execution plan.

Consider voting for OVER clause enhancement request - DISTINCT clause for aggregate functions on the feedback site if you would like this added natively to SQL Server.

share|improve this answer

edited 4 hours ago

Paul White♦

55.6k14293465

answered 6 hours ago

Erik DarlingErik Darling

23.9k1375121

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Wow, I had no idea they've been looking into this since 2008 :-)
  
  – Lennart
  3 hours ago
  

  
  
  
  

add a comment | 

This is how I'd do it:

SELECT *
FROM #MyTable AS mt
CROSS APPLY ( SELECT COUNT(DISTINCT mt2.Col_B) AS dc
 FROM #MyTable AS mt2
 WHERE mt2.Col_A = mt.Col_A
 -- GROUP BY mt2.Col_A 
 ) AS ca;

The GROUP BY clause is redundant, but may give you a better execution plan.

Consider voting for OVER clause enhancement request - DISTINCT clause for aggregate functions on the feedback site if you would like this added natively to SQL Server.

share|improve this answer

edited 4 hours ago

Paul White♦

55.6k14293465

answered 6 hours ago

Erik DarlingErik Darling

23.9k1375121

SELECT *
FROM #MyTable AS mt
CROSS APPLY ( SELECT COUNT(DISTINCT mt2.Col_B) AS dc
 FROM #MyTable AS mt2
 WHERE mt2.Col_A = mt.Col_A
 -- GROUP BY mt2.Col_A 
 ) AS ca;

share|improve this answer

edited 4 hours ago

Paul White♦

55.6k14293465

answered 6 hours ago

Erik DarlingErik Darling

23.9k1375121

edited 4 hours ago

Paul White♦

55.6k14293465

edited 4 hours ago

Paul White♦

55.6k14293465

answered 6 hours ago

Erik DarlingErik Darling

23.9k1375121

answered 6 hours ago

Erik DarlingErik Darling

23.9k1375121

1

create table #MyTable (
Col_A varchar(5),
Col_B int
)

insert into #MyTable values ('A',1)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',3)

insert into #MyTable values ('B',4)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',5)


;with t1 as (

select t.Col_A,
 count(*) cnt
 from (
 select Col_A,
 Col_B,
 count(*) as ct
 from #MyTable
 group by Col_A,
 Col_B
 ) t
 group by t.Col_A
 )

select a.*,
 t1.cnt
 from #myTable a
 join t1
 on a.Col_A = t1.Col_a

share|improve this answer

answered 7 hours ago

kevinnwhatkevinnwhat

68018

add a comment | 

1

create table #MyTable (
Col_A varchar(5),
Col_B int
)

insert into #MyTable values ('A',1)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',3)

insert into #MyTable values ('B',4)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',5)


;with t1 as (

select t.Col_A,
 count(*) cnt
 from (
 select Col_A,
 Col_B,
 count(*) as ct
 from #MyTable
 group by Col_A,
 Col_B
 ) t
 group by t.Col_A
 )

select a.*,
 t1.cnt
 from #myTable a
 join t1
 on a.Col_A = t1.Col_a

share|improve this answer

answered 7 hours ago

kevinnwhatkevinnwhat

68018

add a comment | 

create table #MyTable (
Col_A varchar(5),
Col_B int
)

insert into #MyTable values ('A',1)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',3)

insert into #MyTable values ('B',4)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',5)


;with t1 as (

select t.Col_A,
 count(*) cnt
 from (
 select Col_A,
 Col_B,
 count(*) as ct
 from #MyTable
 group by Col_A,
 Col_B
 ) t
 group by t.Col_A
 )

select a.*,
 t1.cnt
 from #myTable a
 join t1
 on a.Col_A = t1.Col_a

share|improve this answer

answered 7 hours ago

kevinnwhatkevinnwhat

68018

create table #MyTable (
Col_A varchar(5),
Col_B int
)

insert into #MyTable values ('A',1)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',3)

insert into #MyTable values ('B',4)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',5)


;with t1 as (

select t.Col_A,
 count(*) cnt
 from (
 select Col_A,
 Col_B,
 count(*) as ct
 from #MyTable
 group by Col_A,
 Col_B
 ) t
 group by t.Col_A
 )

select a.*,
 t1.cnt
 from #myTable a
 join t1
 on a.Col_A = t1.Col_a

share|improve this answer

answered 7 hours ago

kevinnwhatkevinnwhat

68018

answered 7 hours ago

kevinnwhatkevinnwhat

68018

answered 7 hours ago

kevinnwhatkevinnwhat

68018

1

You can emulate it by using dense_rank, and then pick the maximum rank for each partition:

select col_a, col_b, max(rnk) over (partition by col_a)
from (
 select col_a, col_b
 , dense_rank() over (partition by col_A order by col_b) as rnk 
 from #mytable
) as t 

share|improve this answer

answered 3 hours ago

LennartLennart

13.3k21343

add a comment | 

1

You can emulate it by using dense_rank, and then pick the maximum rank for each partition:

select col_a, col_b, max(rnk) over (partition by col_a)
from (
 select col_a, col_b
 , dense_rank() over (partition by col_A order by col_b) as rnk 
 from #mytable
) as t 

share|improve this answer

answered 3 hours ago

LennartLennart

13.3k21343

add a comment | 

You can emulate it by using dense_rank, and then pick the maximum rank for each partition:

select col_a, col_b, max(rnk) over (partition by col_a)
from (
 select col_a, col_b
 , dense_rank() over (partition by col_A order by col_b) as rnk 
 from #mytable
) as t 

share|improve this answer

answered 3 hours ago

LennartLennart

13.3k21343

select col_a, col_b, max(rnk) over (partition by col_a)
from (
 select col_a, col_b
 , dense_rank() over (partition by col_A order by col_b) as rnk 
 from #mytable
) as t 

share|improve this answer

answered 3 hours ago

LennartLennart

13.3k21343

answered 3 hours ago

LennartLennart

13.3k21343

answered 3 hours ago

LennartLennart

13.3k21343