Count distinct rows on multiple columns

0 pts.
Tags:
Access
Data/Application Integration
Database
DB2
Desktops
Development
Management
Microsoft Windows
Oracle
Oracle Business Applications
Oracle development
OS
PeopleSoft
Programming Languages
Security
Servers
SQL
SQL Server
T-SQL
Windows
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: January 5, 2007  8:23 PM
UPDATED: April 19, 2013  8:11 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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)

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Jklipa
    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
    0 pointsBadges:
    report
  • Epistemology
    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 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following