Count Distinct Rows on Multiple Columns
Home > IT Answers > Microsoft Windows > Count Distinct Rows on Multiple Columns
mjackson
0 pts.
0
Q:
Count Distinct Rows on Multiple Columns
Windows, Programming Languages, Security, Oracle, SQL, Servers, Microsoft Windows, T-SQL, PeopleSoft, DB2, Management, Database, OS, Desktops, Access, Development, SQL Server, Oracle Business Applications, Data/Application Integration, Oracle development
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.
ASKED: Jan 5 2007  8:23 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
SheldonLinker
0 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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)
Last Answered: Jan 5 2007  11:35 PM GMT by SheldonLinker   0 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

jklipa   0 pts.  |   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   0 pts.  |   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!

 
0