group by distinct - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

group by distinct

Author

Topic

kiran
Starting Member

30 Posts

Posted - 2002-06-27 : 18:59:46

Hi, i am getting different results for the following queries

1) SELECT COUNT(DISTINCT column) FROM table WHERE <<condition>>

2) SELECT groupcolumn,COUNT(DISTINCT column) FROM table
WHERE <<condition>> GROUP BY groupcolumn

in the first and second queries the condition is same. I am getting more records(total) in second query. why is this happening ?

thx in advance

olily
Starting Member

37 Posts

Posted - 2002-06-27 : 20:08:50

1) You actually count distinct rows without categorizing or grouping from table according to the condition you specify.

2) You cout the records for each groupcolumn. You might have same record in different groupcolumn. That's why this one return more total.

For example:
table (col1, col2) with values
test, 1
test, 2
test, 3
test, 3
test2, 1
test2, 3
test2, 4
test2, 4
test2, 5

So, from the first sql statement your result is 5 whereas using second sql your result will be as below:
test 3
test2 4

I hope this could clear your doubts.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-27 : 20:13:03

The GROUP BY clause restricts the rows of the result set, there is only one row for each distinct value in the grouping column or columns. Each result set row contains summary data related to the specific value in its grouping columns i.e. it evaluates the aggregate ( count(distinct ) ) for each of the unique values in the grouping column. The where clause is applied before the grouping.The aggregate is applied after.

HTH
Jasper Smith

Edited by - jasper_smith on 06/27/2002 20:14:12

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources