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? |
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. |
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. |
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.
|
Sorry, dup post
|
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 |
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); |
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; 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; |
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 |
Quote:
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. |
Tried that... no joy.
|
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.