Make public class distribution?

[Cilraaz]

Assuming that P99's character table isn't much different from the default PEQ character table, the SQL would just be something like:

SELECT class, count(distinct id) FROM character_ WHERE timelaston > (UNIX_TIMESTAMP() - 604800) AND level > '5' GROUP BY class;

Replace 5 with whatever level you want to check for. That will check for individual characters (not accounts) that have been logged on in the past 7 days, so it would eliminate some mules and unplayed alts from the list. You could even use this to show representation within brackets (ie. "level > '5 AND level < '11'" would give you the 5-10 bracket, etc).

Edit:

Assuming that it wouldn't put too much strain on the SQL server, you could create a PHP script that would display the different brackets. You'd just need to decide what brackets you wanted it chopped by, but it could pull and display say 1-9, 10-19, 20-29, 30-39, 40-49, and 50.

SELECT class, count(distinct id) FROM character_ WHERE timelaston > (UNIX_TIMESTAMP() - 604800) AND level > '0' AND level < '10' GROUP BY class;
SELECT class, count(distinct id) FROM character_ WHERE timelaston > (UNIX_TIMESTAMP() - 604800) AND level > '9' AND level < '20' GROUP BY class;
SELECT class, count(distinct id) FROM character_ WHERE timelaston > (UNIX_TIMESTAMP() - 604800) AND level > '19' AND level < '30' GROUP BY class;
SELECT class, count(distinct id) FROM character_ WHERE timelaston > (UNIX_TIMESTAMP() - 604800) AND level > '29' AND level < '40' GROUP BY class;
SELECT class, count(distinct id) FROM character_ WHERE timelaston > (UNIX_TIMESTAMP() - 604800) AND level > '39' AND level < '50' GROUP BY class;
SELECT class, count(distinct id) FROM character_ WHERE timelaston > (UNIX_TIMESTAMP() - 604800) AND level = '50' GROUP BY class;

[Ferok]

No count?

[Cilraaz]

Yeah, I missed the count and the group by. Trying to get a quick post in during work and guess I went too quick.

[Ferok]

Originally Posted by Cilraaz [You must be logged in to view images. Log in or Register.]

Yeah, I missed the count and the group by. Trying to get a quick post in during work and guess I went too quick.

Still fails because you have terms in your select that aren't in your group by, but I know what you mean. This is probably what you meant:

SELECT class, count(distinct id) FROM character_ WHERE timelaston > (UNIX_TIMESTAMP() - 604800) AND level = '50' GROUP BY class;

Fortunately I'm doing SQL migrations this week so looking up syntax looks an awful lot like work!

[Cilraaz]

http://www.thetimekillers.com/eqemu/test.php

Works fine for me with a limited database to work with. I'll create multiple characters just to fuddle with when I get home tonight.

Took another look at it. Yeah, I don't need the timelaston or level in the select. Drop those and it's a more efficient statement.

[Ferok]

Originally Posted by Cilraaz [You must be logged in to view images. Log in or Register.]

http://www.thetimekillers.com/eqemu/test.php

Works fine for me with a limited database to work with. I'll create multiple characters just to fuddle with when I get home tonight.

Guess it just ignores them, since it doesn't make sense to return them.

I'm a little more familiar with MS-SQL, which I'm pretty sure would throw an error there.

Either way, I'm betting that data like this would be done as a nightly / hourly load to the message board db, rather than an on demand directly from the game db. It's not really very important that it's up-to-the-second information. This kind of aggregation isn't overly costly, but more costly than I'm sure it needs to be.

[Yiblaan]

Thank Prexus I am not a math major

[FatMagic]

I'd like to see if you guys can get this worked out... it would be cool to see the class distribution on the server.

[Cilraaz]

Originally Posted by FatMagic [You must be logged in to view images. Log in or Register.]

I'd like to see if you guys can get this worked out... it would be cool to see the class distribution on the server.

It would actually be pretty simple to do. It's more a matter of whether the admins want to publicize that information.

[BornOfAshes]

assuming that the database is MS SQL... I don't think it is though.

but yeah, you should be able to generate a logdump of the active players and then sort them like Ferok suggested above.

I for one would enjoy seeing the results of the queries, it's kind of neat to see trends