Home > IT Answers > Database > Count Distinct Rows on Multiple Columns
Help

Question

  Asked: Jan 5 2007   8:23 PM GMT
  Asked by: mjackson

Count Distinct Rows on Multiple Columns


Database, Oracle, DB2, Microsoft Windows, OS, Servers, Security, Desktops, Management, Access, SQL Server, Windows, Oracle Business Applications, Data/Application Integration, PeopleSoft, Oracle development, SQL, Development, Programming Languages, T-SQL

I want to write a single query that counts each distinct Student ID from the following sample data.

Sample Data:

ID NAME PLAN DESCR
--- ------- ---- -------------------
101 Michael 010C Cafe- 10.00 Dollars
102 Adam 010C Cafe- 10.00 Dollars
102 Adam 05CC Cafe- 5.00 Dollars
103 Jane 010C Cafe- 10.00 Dollars
104 Sarah 010C Cafe- 10.00 Dollars
104 Sarah 05CC Cafe- 5.00 Dollars
105 Tom 010C Cafe- 10.00 Dollars

Expected Results:

CNT ID NAME PLAN DESCR
--- --- ------- ---- -------------------
1 101 Michael 010C Cafe- 10.00 Dollars
1 102 Adam 010C Cafe- 10.00 Dollars
102 Adam 05CC Cafe- 5.00 Dollars
1 103 Jane 010C Cafe- 10.00 Dollars
1 104 Sarah 010C Cafe- 10.00 Dollars
104 Sarah 05CC Cafe- 5.00 Dollars
1 105 Tom 010C Cafe- 10.00 Dollars
---

5 Total Students

I believe I may need to use the SELECT COUNT(DISTINCT ID) FROM ...GROUP BY ID, NAME, PLAN, DESCR
Well, here's where I get lost. Any ideas?

Thanks,
M.J.

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
Last Answered: Jan 5 2007  11:35 PM GMT  by SheldonLinker




If you just wanted a count of the student IDs present, you could use COUNT(DISTINCT id). However, you want a running tally-mark. This involves sorting the file to temporary storage, then looking for changes, and putting a 1 in the slot where you have the changes:

WITH sorted AS(
SELECT *
FROM sampleData
ORDER BY id)
SELECT CASE WHEN COALESCE(b.id,0)<a.id THEN 1 END cnt,
a.*
FROM sorted a
LEFT OUTER JOIN sorted b ON
RRN(a)-1=RRN(b)
ORDER BY RRN(a);

The RRN function is Relative Record Number, and basically allows us to use record position, rather than data in the record. This works on DB2 only.
---
Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Database, Oracle and Microsoft Windows.

Looking for relevant Database Whitepapers? Visit the SearchOracle.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

jklipa  |   Jan 8 2007  8:50AM GMT

If all you want is a count showing total students by unique ID then why not use this simple select statement…:

select ID, count(*) from mylib/myfile
group by ID
order by ID

And if you’re interested in counting duplicate records you can use:
select ID, count(*) from mylib/myfile
group by ID
having Count(*) > 1
order by ID

hth

 

epistemology  |   Jan 8 2007  11:42AM GMT

To count the records for each student in 9i you use the following analytical function (do not use a GROUP BY):
SELECT ROW_NUMBER() OVER (PARTITION BY ID) AS CNT,
ID, NAME, PLAN, DESCR
FROM MY_TABLE;

To get the exact result shown where only the first row has a “1″ and the other rows are NULL
SELECT DECODE(CNT,1,1,NULL) CNT, ID, NAME, PLAN, DESCR
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY ID) AS CNT,
ID, NAME, PLAN, DESCR
FROM MY_TABLE);

Try it!