SQL query: multiply count() IF another value = x-CodePudding

I'm working on a query to count instances of duplicates and sort them for a leaderboard.

What I am trying to do is

count occurances of that owner (id). IF that "rarity" column of that row is 2, I want it to count as 10. If it's 3, it should count as 100.

Example of working count (that does not multiply) I started from:

SELECT owner, COUNT(*) AS 'count'
FROM 'usercards' AS 'usercards' WHERE ('usercards'.'group' LIKE '%$group%' AND 'usercards'.'customCard' = 0 AND 'usercards'.'rarity' = 1)
GROUP BY owner
ORDER BY count DESC;

here is another example of what I have tried. I have many more examples that have not worked.

SELECT rarity, owner
FROM 'usercards' AS 'usercards' WHERE ('usercards'.'group' LIKE '%$group%' AND 'usercards'.'customCard' = 0)
IF (rarity = 1)
SELECT (COUNT(*) * 1) AS 'count'
FROM 'usercards' AS 'usercards' WHERE ('usercards'.'group' LIKE '%$group%' AND 'usercards'.'customCard' = 0)
GROUP BY owner
ORDER BY count DESC;

I'm trying to do this in SQL because it should be faster than node. Is it possible that the query is getting so complex that it might not be?

CodePudding user response：

I'm not sure about the structure of your table, but looks like the MySQL POWER function is what you're needing.

POWER(M,N);

With what you describe (rarity 2 = 10, rarity 3 = 100) then you're using a power/exponent of the form

"10 to the power of rarity - 1"

(So 10^0 = 1, 10^1 = 10, 10^2 = 100, 10^3 = 1000).

To achieve a multiplication on the "counts" then you'd use something like this:

SELECT owner, COUNT(*) * POWER(10, rarity - 1) FROM `usercards`  AS 'usercards' WHERE ....

You'll need to decide how to approach the SELECT part of the query based on your table structure and conditions, as well as the GROUP BY clauses.

CodePudding user response：

You'll want to use a case statement for conditionally multiplying based on the value of another column.

Something like this:

SELECT owner, rarity,
       COUNT(*) AS 'count', 
       CASE 
           WHEN rarity = 1 THEN COUNT(*) 
           WHEN rarity = 2 THEN COUNT(*) * 10 
           WHEN rarity = 3 THEN COUNT(*) * 100 
       END AS 'modifiedCount'
FROM 'usercards' AS 'usercards' 
WHERE 'usercards'.'group' LIKE '%$group%' AND 'usercards'.'customCard' = 0
GROUP BY owner, rarity
ORDER BY count DESC;