EQEmulator Forums

EQEmulator Forums (https://www.eqemulator.org/forums/index.php)
-   Support::Windows Servers (https://www.eqemulator.org/forums/forumdisplay.php?f=587)
-   -   SQL Query help (https://www.eqemulator.org/forums/showthread.php?t=35275)

reddogut 04-28-2012 08:01 PM

SQL Query help
 
Can someone help me with an SQL query?

I want to see what the average hitpoints are for every mob/npc at a specific level.... For example, what is the average ammount of hitpoints for all level 45 mobs/npcs?

lerxst2112 04-28-2012 08:47 PM

SELECT AVG(`hp`) FROM `npc_types` WHERE `level` = 45;

You may want to add to the WHERE so you don't select guards, pets, etc. You'd just need to figure out how to select them so you can exclude them.

reddogut 04-28-2012 09:00 PM

How about this?

Select AVG(hp) from npc_types where level = 45 AND merchant_id = 0 AND 'name' NOT LIKE '%Guard%' AND 'name' NOT LIKE '%pet%' AND 'name' NOT LIKE '%Sum%';

I get 4224 average hitpoints out of this query.

lerxst2112 04-28-2012 09:16 PM

It's up to you how you select them, but keep in mind "%Guard%" matches "Guard Bob" and "Marshmallow Guardian", and "%pet%" matches anything with 'pet' anywhere in the name, like the mythical carpet monster, etc. You would most likely want to run all of the where queries alone to make sure you're not picking up anything you want included in the average and adjust to not exclude them. In case you didn't know, you can use something like "Guard%" to only select names where they begin with "Guard". To exclude pets I would probably check against the npcID in the pets table rather than trying to do it by name.

reddogut 04-28-2012 09:46 PM

Sorry, dup post

reddogut 04-29-2012 04:38 PM

Ok, so I want to change the hitpoints of all level 50 mobs... But I don't want to change the hitpoints for Guards, pets or merchants. So I came up with this...

UPDATE npc_types SET hp = 3000 WHERE LEVEL = 50 AND merchant_id = 0 AND name NOT LIKE 'Guard_%';

Now AFAICT the id's for the pets in the npc_types are all between 501 and 770. What should I add to the above query to exclude those id's?

Thanks,
Mike

Derision 04-29-2012 04:52 PM

Code:

UPDATE npc_types SET hp = 3000 WHERE LEVEL = 50 AND merchant_id = 0 AND name NOT LIKE 'Guard_%' AND (id < 501 OR id > 770);

lerxst2112 04-29-2012 06:00 PM

You should probably do a query with what you think you want to change and look at the results to make sure there's nothing in it you don't want to change first.

Something like this:
Code:

SELECT `id`,`name`,`hp` FROM `npc_types` WHERE `level` = 50 && `merchant_id` = 0 && `name` NOT LIKE "Guard%" AND `id` NOT IN (SELECT `npcid` FROM `pets`) ORDER BY `hp` DESC;
It looks like you might want to exclude Priest of Discord, some bankers, maybe the defenders, boats, spires, bouncers, etc. You could probably limit it to not change anything over a particular hp value except there's some fabled mobs mixed in with the high hp stuff that you might want to change. *shrug*

The method I used to exclude pets is exact but a little less efficient that the id range. It also may not be what you want since there's a few NPC pets "#Pixtt_%" in the table that would get skipped. My pet table appears to have all of the player pets in the range of 500 to 685, but my database is a little old. You can check yours like this:

Code:

SELECT `type`,`npcid` FROM `pets` ORDER BY `npcid` DESC;
There's not that many, so you can just scroll through the list and see where to start and stop, or if there are any you do want to reduce in that range. I'm not entirely sure how some of them are summoned.

reddogut 04-30-2012 10:40 PM

I wanted to take a moment and thank everyone for helping me with my mysql questions.... Now, how about this one?

When I run

UPDATE npc_types SET STR = AVG(STR) WHERE LEVEL = 50;

I get a MYSQL error 1111, invalid use of group function

I scoured the internet to try to figure out why and I can't seem to find it.

Thanks again,

Mike

Raewan 04-30-2012 11:14 PM

Quote:

Originally Posted by reddogut (Post 209377)
I wanted to take a moment and thank everyone for helping me with my mysql questions.... Now, how about this one?

When I run

UPDATE npc_types SET STR = AVG(STR) WHERE LEVEL = 50;

I get a MYSQL error 1111, invalid use of group function

I scoured the internet to try to figure out why and I can't seem to find it.

Thanks again,

Mike


instead of LEVEL , make it `LEVEL`, its looking at the word level like a command not a column name so you have to surround it with the grave accent (backtick) symbol.

reddogut 04-30-2012 11:57 PM

Tried that... no joy.

lerxst2112 05-01-2012 12:12 AM

Just run the query to get the average and use that hardcoded number.


All times are GMT -4. The time now is 03:11 AM.

Powered by vBulletin®, Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.