SQL Query help

[reddogut]

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]

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]

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]

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]

Sorry, dup post

[reddogut]

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]

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]

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:

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:

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]

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]

Originally Posted by reddogut

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]

Tried that... no joy.

[lerxst2112]

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