CODEWITHSUNDEEP
mysql
Mobidi
Mobidi

Reputation: 95

MySQL Group by only if condition

I'm having hard time trying to find if I can do this. So, from one table, I want to use GROUP BY only if the w.type is 'object' the other object don't have to be grouped.

SELECT COUNT(*) as number, w.name, w.type
FROM storage AS s
LEFT JOIN weapon AS w ON w.id=s.weapon_id
WHERE s.owner_id = 'ID'
GROUP BY s.name
ORDER BY w.type

As you can see, this query group by everything.

I tried with UNION but it seems compromised because the double query is on the same table. And I looked for Case, but I'm not sure how to use it.

Thanks for reading !

Edit : This is for displaying inventory in game. Each weapons buckler are unique but you can have few different weapons with the same w.name. Objects don't have this constraint, I need them group by and show to the player the number of the same item.

Player will see this :

Player Inventory

Diamond Sword, Iron Sword, Iron Sword

Apple x2, Pear x3, Egg x1

Upvotes: 0

Views: 236

Answers (1)

Chris J
Chris J

Reputation: 1447

You mention you've tried a UNION, but unless I'm just tired and missing something, does this not work?

SELECT COUNT(*) as number, w.name, w.type 
FROM storage AS s 
LEFT JOIN weapon AS w ON w.id=s.weapon_id 
WHERE s.owner_id = 'ID' AND w.type = 'Object' 
GROUP BY s.name 
UNION
SELECT 1 as number, w.name, w.type 
FROM storage AS s 
LEFT JOIN weapon AS w ON w.id=s.weapon_id 
WHERE s.owner_id = 'ID' AND w.type != 'Object' 
GROUP BY w.id
ORDER BY w.type

In the second select, the group by would be a unique identifier - I'm assuming name and type would be sufficient, but I'm shooting blind without seeing data

Upvotes: 1

Related Questions