Select Count on Column. (not so simple)

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 2005 Forums

Transact-SQL (2005)

Select Count on Column. (not so simple)

Author

Topic

smilbuta
Starting Member

5 Posts

Posted - 2007-07-24 : 16:43:23

OK ill try to explain.

Here is a sample table of data....

Id Col1 Col2
1  1    1
2  1    2
3  2    1
4  2    3
5  3    1
6  3    3
7  3    2
8  4    3
9  4    3
10 4    3

I have been trying to build a query that looks at the table and counts how many distinct items are in Col2 then groups it by Col1.

The end Result would lool like the following output.

Details: Look at the Sample table above. Notice how Record 8-10 all have the same value in Col2, this is what i want to count distinct values of. THus in the sample result table, I want to return the group from Col1 of 4 and how many distinct values associated with it from Col2, in this case only 1 since all the valeus in Col2 are the same.

Any help would be apreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 16:46:35

SELECT Col1, COUNT(DISTINCT Col2)
FROM Table1
GROUP BY Col1
ORDER BY Col1

E 12°55'05.76"
N 56°04'39.42"

smilbuta
Starting Member

5 Posts

Posted - 2007-07-24 : 20:27:44

Wow.. I did not know you can put a Distinct statement within an agregate function.
Thankx Peso.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources