SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Count Distinct


Count Distinct

Author
Message
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8839 Visits: 7281
Comments posted to this topic are about the item Count Distinct

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27249 Visits: 13268
Nice question, but a hard one.
I spent quite some time looking at the query to find some hidden error.
But apparently there wasn't any :-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Carlo Romagnano
Carlo Romagnano
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4893 Visits: 3326
I get it right because of excluding the wrong choices.

I run on tuttopodismo
sharath.chalamgari
sharath.chalamgari
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1472 Visits: 798
Nice Question,needs little bit time to go through the Query
Mayank Parmar
Mayank Parmar
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 100
I was sure that "we can Aggregate by columns not included in the SELECT", but I wasn't aware of that use of DISTINCT in Aggregate function, so I had to experiment the same on my own database.

Great Question, really learned something new.

Regards,

--------------------------------------------------------------------------------
Mayank Parmar
Software Engineer

Clarion Technologies
SEI CMMI Level 3 Company

8th Floor, 803, GNFC info Tower,
SG Highway, Ahmedabad - 380 054,
Gujarat, India.
www.clariontechnologies.co.in

Email: mayank.parmar@clariontechnologies.co.in
MSN : mayank.parmar@clariontechnologies.co.in
Mobile: +91 9727748789
--------------------------------------------------------------------------------
cengland0
cengland0
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1676 Visits: 1300
Carlo Romagnano (1/21/2011)
I get it right because of excluding the wrong choices.

Exactly how I did it too.
rollec2k5
rollec2k5
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 98
I was not aware about, that you can use Columns in a having Clause without having the Column in the SELCET.

However, a nice one.

Greetings
R2d2
Daniel Bowlin
Daniel Bowlin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4090 Visits: 2629
I have never thought of using count and count distinct in a having clause the way you do. Brilliant. Thanks.
John Hanrahan
John Hanrahan
SSC Eights!
SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)

Group: General Forum Members
Points: 803 Visits: 1464
I tested this. It doesn't seem to work correctly everytime. But I had never thought of using distinct in a having clause, so nice.
john.arnott
john.arnott
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1968 Visits: 3059
rollec2k5 (1/21/2011)
I was not aware about, that you can use Columns in a having Clause without having the Column in the SELCET.

Not exactly.... The HAVING clause can use aggregates like COUNT(), MAX(), SUM() and so on which themselves refer to other columns, but it may not directly refer to a column not in the GROUP BY clause which in turn can only use columns or non-aggregate expressions in the SELECT.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search