Very complex - How to count difference data

Question

Post reply

Very complex - How to count difference data

nguyennd

SSCommitted

Points: 1881
More actions
March 5, 2010 at 8:12 pm

#230589

CREATE TABLE [dbo].[AAATable](
[col1] [int] NULL,
[col2] [char](1) NULL,
[col3] [char](2) NULL
) ON [PRIMARY]
insert into AAATable values (1,'a','aa');
insert into AAATable values (1,'a','aa');
insert into AAATable values (1,'a','bb');
insert into AAATable values (2,'a','bb');
insert into AAATable values (2,'b','bb');
insert into AAATable values (2,'c','bb');
insert into AAATable values (2,'c','bb');
col1col2col3C_col21C_col31
1a aa12
1a aa 12
1a bb 12
2a bb 31
2b bb 31
2c bb 31
2c bb 31
which
C_col21 column count difference data of col2 column
C_col31 column count difference data of col3 column
Please help me to select data as describe !
http://unitech.vn

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

What is the logic beyond the columns C_col21 and C_col31? And how you determined the values?

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

Good table and data post... but I don't have a clue as to what you want for columns 21 and 31. Care to explain a bit?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nguyennd SSCommitted Points: 1881 More actions · Answer 3

arun.sas (3/5/2010)
Hi,
What is the logic beyond the columns C_col21 and C_col31? And how you determined the values?

C_col21: count distinct col2 by col1

C_col31: count distinct col3 by col1

Regard

http://unitech.vn

arun.sas SSChampion Points: 11831 More actions · Answer 4

nguyennd (3/5/2010)
C_col21: count distinct col2 by col1
C_col31: count distinct col3 by col1

Hi

count(distinct col2) = 3 and count(distinct col3)= 2

then, Is you want to divided the value with col1 count?

nguyennd SSCommitted Points: 1881 More actions · Answer 5

arun.sas (3/5/2010)
nguyennd (3/5/2010)
C_col21: count distinct col2 by col1
C_col31: count distinct col3 by col1
Hi
count(distinct col2) = 3 and count(distinct col3)= 2
then, Is you want to divided the value with col1 count?

I want to write SQL like that:

count(distinct col2) over (partition by col1) as C_col21

but SQLSERVER cannot support 😀

http://unitech.vn

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

Divide'n'Conquer...

WITH

cteCounts AS

(

SELECT Col1,

COUNT(DISTINCT Col2) AS C_col21,

COUNT(DISTINCT Col3) AS C_col31

FROM dbo.AAATable

GROUP BY Col1

)

SELECT original.Col1, original.Col2, original.Col3,

counts.C_col21, counts.C_col31

FROM dbo.AAATable original

INNER JOIN cteCounts counts

ON original.Col1 = counts.Col1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nguyennd SSCommitted Points: 1881 More actions · Answer 7

Thank Jeff Moden 😀

I were try this SQL but it complex, any one else?

SELECT

Col1,

Col2,

Col3,

Max(Rnk2) OVER (PARTITION BY Col1) as C_col21,

Max(Rnk3) OVER (PARTITION BY Col1) as C_col31

FROM

(SELECT

Col1,

Col2,

Col3,

Dense_Rank() OVER (PARTITION BY Col1 ORDER BY Col2) as rnk2,

Dense_Rank() OVER (PARTITION BY Col1 ORDER BY Col3) as rnk3

FROM

AAATable) T

http://unitech.vn

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 8

nguyennd (3/5/2010)
Thank Jeff Moden 😀
I were try this SQL but it complex, any one else?
SELECT
Col1,
Col2,
Col3,
Max(Rnk2) OVER (PARTITION BY Col1) as C_col21,
Max(Rnk3) OVER (PARTITION BY Col1) as C_col31
FROM
(SELECT
Col1,
Col2,
Col3,
Dense_Rank() OVER (PARTITION BY Col1 ORDER BY Col2) as rnk2,
Dense_Rank() OVER (PARTITION BY Col1 ORDER BY Col3) as rnk3
FROM
AAATable) T

For the given problem, it also uses about 20 more reads, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 9

On the outside chance that there's anyone out there that thinks this type of thing can't be done in SQL Server 2000...

SELECT original.Col1, original.Col2, original.Col3,

counts.C_col21, counts.C_col31

FROM dbo.AAATable original

INNER JOIN (

SELECT Col1,

COUNT(DISTINCT Col2) AS C_col21,

COUNT(DISTINCT Col3) AS C_col31

FROM dbo.AAATable

GROUP BY Col1

) counts

ON original.Col1 = counts.Col1

A CTE and a "Derived Table" are functionally identical if you only need to refer to the "Derived Table" once. I prefer the CTE simply because I like the "top down" logic, but the "Derived Table" works just as well in solving problems like this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arun.sas SSChampion Points: 11831 More actions · Answer 10

Hi Jeff,

Very cheerful,

Credit to explain in both the versions

🙂

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 11

Thanks, Arun. You're a gentleman and a scholar especially for giving me the heads up on the OP's additional response.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 12

Here's a CROSS APPLY version of Jeff's derived table solution:

drop TABLE #AAATable

CREATE TABLE #AAATable(

[col1] [int] NULL,

[col2] [char](1) NULL,

[col3] [char](2) NULL

) ON [PRIMARY]

insert into #AAATable values (1,'a','aa');

insert into #AAATable values (1,'a','bb');

insert into #AAATable values (2,'a','bb');

insert into #AAATable values (2,'b','bb');

insert into #AAATable values (2,'c','bb');

SELECT col1, col2, col3, iTVF.C_col21, iTVF.C_col31

FROM #AAATable a

CROSS APPLY

(SELECT

C_col21 = COUNT(DISTINCT col2),

C_col31 = COUNT(DISTINCT col3)

FROM #AAATable

WHERE col1 = a.col1) iTVF

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

nguyennd SSCommitted Points: 1881 More actions · Answer 13

😀 many many pro SQL

I like ChrisM@home's SQL. I compare 3 SQL and the best performance is: My SQL :-D:-D

http://unitech.vn

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 14

You may want to vote for this

http://connect.microsoft.com/SQLServer/feedback/details/254393/over-clause-enhancement-request-distinct-clause-for-aggregate-functions

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537