Number of pages created per user.

Morning all,

I was hoping someone can help me with this as I'm starting to get pretty desperate.

The below code will generate a table with a list of how many pages each user has created. No matter what I try, I can't figure out how to get the actual username to appear. The User field only displays a bunch of random text and numbers.

 


Code:

select concat('[~', creator, ']') as "User", count(distinct CONTENT.title) as "Pages created"
    from CONTENT
    where creator IS NOT NULL
    group by creator
    order by "Pages created" desc;

Result:

pagescreated.jpg

1 answer

1 accepted

1 vote
Daniel Eads Community Champion Jul 20, 2016

Hi Martin,

The Creator column in the Content table is just a unique ID for the user. The actual usernames and name for users are in the cwd_user table. It's also worth noting that your query will count not only pages, but attachments, status updates, etc. The queries here are scoped to only include Page and Blog content types. Try this out:

SELECT lower_username AS "Username", display_name AS "Full Name", count(DISTINCT CONTENT.title) AS "Pages created"
FROM CONTENT
LEFT JOIN USER_MAPPING ON CONTENT.creator = USER_MAPPING.user_key
LEFT JOIN CWD_USER ON USER_MAPPING.lower_username = CWD_USER.lower_user_name
WHERE creator IS NOT NULL
AND CONTENTTYPE IN ('PAGE', 'BLOGPOST')
GROUP BY creator
ORDER BY "Pages created" DESC;

 

For future people reading this with Oracle databases, you'll need to change things around so Oracle actually executes the query instead of griping at you. The previous query should be MySQL/MSSQL compatible (according to posts complaining about the Oracle problem I hit), but we only have Oracle Confluence databases so I couldn't confirm. Give this one a go if the above threw an error.

SELECT DISTINCT lower_username AS "Username", display_name AS "Full Name", "Pages created"
FROM (SELECT creator, count(DISTINCT CONTENT.title) AS "Pages created"
FROM CONTENT
WHERE creator IS NOT NULL
AND CONTENTTYPE IN ('PAGE', 'BLOGPOST')
GROUP BY creator
ORDER BY "Pages created" DESC)
LEFT JOIN USER_MAPPING ON creator = USER_MAPPING.user_key
LEFT JOIN CWD_USER ON USER_MAPPING.lower_username = CWD_USER.lower_user_name
ORDER BY "Pages created" DESC;

This is absolutely  spot on thank you so much for helping me with this, you have helped me out massively. I didn't realise the user_mapping table existed because it wasn't shown here.

Guys, I get the below error when running the following query 

ERROR: column "user_mapping.lower_username" must appear in the GROUP BY clause or be used in an aggregate function

Query:

SELECT lower_username AS "Username", display_name AS "Full Name", count(DISTINCT CONTENT.title) AS "Pages created"
FROM CONTENT
LEFT JOIN USER_MAPPING ON CONTENT.creator = USER_MAPPING.user_key
LEFT JOIN CWD_USER ON USER_MAPPING.lower_username = CWD_USER.lower_user_name
WHERE creator IS NOT NULL
AND CONTENTTYPE IN ('PAGE', 'BLOGPOST')
GROUP BY creator
ORDER BY "Pages created" DESC;

 

 

Any ideas? 

Got if fixed, just need to group by all the columns. 

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Thursday in Confluence

Three common content challenges + how to manage them

An efficient enterprise content management system, or ECM, is a must-have for companies that create work online (cough   cough, all companies). If content calendars, marketing plans, and bu...

80 views 0 5
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you