Last post Jan 27, 2006 04:45 PM by TimGT
Jan 27, 2006 01:37 PM|TimGT|LINK
I was wondering if somebody could help me with a SQL query to gather some base DNN stats. I am trying to build a query that is very close to what is show when you go to Host->Portals and it displays the portals.
I'm looking for a query that will display to me all the portals, ordered by the number of users in a portal, outputted with the portalalias associated.
So the SQL query would output this:
and so on.
Could somebody help with this please? I'm not very good at doing complicated SQL commands. Thx!
Jan 27, 2006 01:51 PM|TimGT|LINK
My guess was this as a query, however, it doesn't work:
'Users' = ( select count(*) from UserPortals where UserPortals.PortalId = P.PortalId )
'PortalAliasID' = ( select HTTPAlias from PortalAlias where PortalAlias.PortalID = P.PortalId )
from Portals P
order by P.Users DESC
Could somebody help fix this please so it outputs "PortalAlias Users"
Jan 27, 2006 02:31 PM|kparker|LINK
How about this.......
SELECT dbo.UserPortals.PortalId, COUNT(dbo.UserPortals.UserId) AS UserCount, dbo.Portals.PortalName, dbo.PortalAlias.HTTPAlias
FROM dbo.UserPortals INNER JOIN
dbo.Portals ON dbo.UserPortals.PortalId = dbo.Portals.PortalID INNER JOIN
dbo.PortalAlias ON dbo.Portals.PortalID = dbo.PortalAlias.PortalID
GROUP BY dbo.UserPortals.PortalId, dbo.Portals.PortalName, dbo.PortalAlias.HTTPAlias
Jan 27, 2006 03:54 PM|TimGT|LINK
Very nice! Awesome!
Could I ask you to to maybe add 1 little thing? Could you add a statement to only display where UserCount is greater than 10?
THX a ton! :)
Jan 27, 2006 04:40 PM|kparker|LINK
Jan 27, 2006 04:45 PM|TimGT|LINK
I've been trying to figure this out for some time, hah. It is difficult to judge the site of your portals when your site has like 1400.