Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Count Distinct Expand / Collapse
Author
Message
Posted Thursday, January 20, 2011 10:37 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:05 PM
Points: 3,984, Visits: 5,237
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”
Post #1051332
Posted Friday, January 21, 2011 12:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1051362
Posted Friday, January 21, 2011 1:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 2,542, Visits: 2,410
I get it right because of excluding the wrong choices.
Post #1051369
Posted Friday, January 21, 2011 2:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 10:39 AM
Points: 1,194, Visits: 787
Nice Question,needs little bit time to go through the Query
Post #1051382
Posted Friday, January 21, 2011 2:39 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, September 21, 2011 4:03 AM
Points: 713, 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
--------------------------------------------------------------------------------
Post #1051394
Posted Friday, January 21, 2011 3:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
Carlo Romagnano (1/21/2011)
I get it right because of excluding the wrong choices.

Exactly how I did it too.
Post #1051402
Posted Friday, January 21, 2011 4:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 9:21 AM
Points: 153, Visits: 78
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
Post #1051431
Posted Friday, January 21, 2011 7:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 AM
Points: 2,817, Visits: 2,563
I have never thought of using count and count distinct in a having clause the way you do. Brilliant. Thanks.
Post #1051518
Posted Friday, January 21, 2011 8:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 407, Visits: 1,025
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.
Post #1051580
Posted Friday, January 21, 2011 9:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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.

Post #1051634
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse