Report using db2 database

0 pts.
Tags:
DB2
Development
Software Quality Assurance
Hi! I created a report showing columns Member Number ID( Social Security Number) and Number of Visits for that member. Actual requirement is the report should give how many people visited how many times. (i.e. 20 people visited 5 times. 67 peopele visited 30 times. So columns required in the report are Number of people and Number of visits. Can anyone please guide me how am I going to achieve it in ? Thanks
ASKED: May 2, 2005  1:46 PM
UPDATED: May 2, 2005  2:20 PM

Answer Wiki

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

Depending on how your table is structured,

Assuming there is one record per visit and the table is structured like this:

MemberID varchar2(9)
VistDate date

Not sure about Db2; but with Oracle, you could use an inline view like this:

select Count(MemberID) NumberPeople, NumberVisits
from (select MemberID, count(MemberID) NumberVisits
from MemberVisits
group by MemberID)
group by NumberVisits;

If the records in the table look like this:

SQL> select * from membervisits;

MEMBERID VISITDATE
——— ———
1 05-APR-01
1 05-APR-04
1 05-APR-10
2 05-APR-10
2 05-APR-11
3 05-APR-11
3 05-APR-12

7 rows selected.

Then the result would look like this:

SQL> select Count(MemberID) NumberPeople, NumberVisits
2 from (select MemberID, count(MemberID) NumberVisits
3 from MemberVisits
4 group by MemberID)
5 group by NumberVisits;

NUMBERPEOPLE NUMBERVISITS
———— ————
2 2
1 3

Discuss This Question:  

 
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

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