Count Users by Organization

cantyman

I have a list of users in PostgreSQL that belong to various organizations. They are identified by their name, username and their email address. Their email address contains the domain which indicates to which org they belong (e.g. mike@att.com - would indicate that Mike is from AT&T, burt@verizon.com would indicate that Burt is from Verizon).

I need to create a table or chart (whichever is best) that displays each organization and the number of users that belong to the organization.

What I've done so far:
1. My SQL statement: "select user.email, count(*) from user group by user.email"
2. I created a string-based computed column, called Organization, by writing an expression that parses the organization from the email domain. It correctly lists the organization for each user.

EMAIL | ORG
mike@att.com | ATT
burt@verizon.com | VERIZON
jim@att.com | ATT
paul@sprint.com | SPRINT
jeff@apple.com | APPLE
mitch@google.com | GOOGLE
marty@apple.com | APPLE
jen@att.com | ATT
james@sprint.com | SPRINT

I need to summarize the above in a chart or table like so:
ORG | USERS
ATT | 3
VERIZON | 1
APPLE | 2
SPRINT | 2

Where I'm stuck:
1. How and where do I aggregate on Organization (a computed column) to show how many users belong to each organization?
2. A next step would be to show the Top 10 organizations (based on number of users)

Thanks so much for the help. I've been at this for a couple of days now. Hopefully, there's an easy solution or someone's already solved this.

Best Regards,
Mike

Environment: BIRT 2.5.1, PostgreSQL data source

mwilliams

Hi Mike,

You would simple just need to drag your dataSet out into the report to create a table out of it, right click on the detail row tab, choose insert group, group the table on the Organization field, drag an aggregation over into your group header from the palette, choose COUNT as the aggregation type and select to aggregate over your organization grouping. Finally, delete your detail row and you should have a report that shows the count for each organization. Hope this helps. Let me know if you have questions.