CODEWITHSUNDEEP
mysql
user2228240
user2228240

Reputation: 13

Select same SUM from table and same column two different ids

i have a problem & I'm new in this.

i have a table called "items" and there is IDs column and count column also i got a characters table which are the owners of the items ids

i have here a code that working..and shows the top character with the item and its count.. to explain more..

"SELECT SUM(it.count) FROM characters ch  
LEFT OUTER JOIN items it ON ch.charId=it.owner_id
WHERE item_id=40001 ORDER BY SUM(it.count) DESC LIMIT 20");)

So what i want exactly is to make this takes a list of a second item id from same items table and same column count ...

anyone can help me ?

Upvotes: 0

Views: 1697

Answers (1)

Hazzit
Hazzit

Reputation: 6882

For a start, let me make your query a bit more readable:

SELECT SUM(it.count)
FROM characters ch 
LEFT OUTER JOIN items it ON ch.charId=it.owner_id 
WHERE item_id=40001 
ORDER BY SUM(it.count) DESC 
LIMIT 20

This query is a bit weird. First you are selecting one sum (you are not grouping by anything) then you are orderbing by that sum, and limiting the number of returned rows to 20?

Also, you're doing a LEFT OUTER JOIN but then using the outer table in your WHERE table, which results in all rows being removed that only have a character but no item.

The following query would do the exact same thing.

SELECT SUM(it.count)
FROM characters ch 
JOIN items it ON ch.charId=it.owner_id 
WHERE item_id=40001

Now if you want more than one item_id, you either want the total count of both:

SELECT SUM(it.count)
FROM characters ch 
JOIN items it ON ch.charId=it.owner_id 
WHERE item_id IN (40001,40002)

Or you want sums for each item

SELECT SUM(it.count)
FROM characters ch 
JOIN items it ON ch.charId=it.owner_id 
GROUP BY item_id
ORDER BY SUM(it.count) DESC 
LIMIT 20

You could change the JOIN to do just a few item types:

SELECT SUM(it.count)
FROM characters ch 
JOIN items it ON ch.charId=it.owner_id AND it.item_id IN (40001,40002)
GROUP BY item_id
ORDER BY SUM(it.count) DESC 
LIMIT 20

Upvotes: 2

Related Questions