mysql count with multiple groups

Question

Well. I don't really know if what I want is possible by the way I built my table. Let me explain.

I got this table

CREATE TABLE `kill_log` (
  `time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `killer_cid` int(11) NOT NULL,
  `killed_cid` int(11) NOT NULL,
  `map` varchar(25) NOT NULL DEFAULT '',
  KEY `killer_cid` (`killer_cid`),
  KEY `killed_cid` (`killed_cid`)
)

When somebody kills someone in the game, it gets registered, and in the end of each month I need to calculate the winner by calculating how many kills and how many times he was killed.

I tried building up a query with group, but I couldn't figure out how to calculate how many times he was killed..

SELECT killer_cid as char_id, count(killer_cid) as kills
FROM kill_log
WHERE `time` >= (NOW() - INTERVAL 30 DAY)
GROUP BY killer_cid
ORDER BY count(killer_cid) DESC

But what I really need is to show

char_id  |  kills  |  deaths  |  points (kills - deaths)

How could I do this? Should I create a different table and register stuff in another way?

I think I could register when they get killed and when they die, and then group by char_id and count if they killed or died.. but.. is by this method possible?

Holger Brandt · Accepted Answer · 2012-06-29 17:22:01Z

2

SELECT U.char_ID, SUM(U.kills) as kills, SUM(U.killed) as deaths, SUM(U.kills)- SUM(U.killed) as points FROM
(
SELECT killer_cid as char_id, count(killer_cid) as kills, 0 as killed
FROM kill_log
WHERE `time` >= (NOW() - INTERVAL 30 DAY)
GROUP BY killer_cid
ORDER BY count(killer_cid) DESC

UNION

SELECT killed_cid as char_id,0 AS kills, count(killer_cid) as killed
FROM kill_log
WHERE `time` >= (NOW() - INTERVAL 30 DAY)
GROUP BY killed_cid
ORDER BY count(killer_cid) DESC
) AS U
GROUP BY U.char_ID
ORDER BY SUM(U.kills)- SUM(U.killed) DESC

answered Jun 29, 2012 at 17:22

Holger Brandt

4,3541 gold badge22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eggyal Over a year ago

Actually, I like your answer better so have just deleted mine. +1

herenow Over a year ago

thanks man, that is perfect.. didnt know i could use from like that.

Puggan Se · Accepted Answer · 2012-06-29 17:15:24Z

0

select
 killer.killer_cid as cid,
 count(distinct killer.killed_cid, killer.time) as kills,
 count(distinct killed.killer_cid, killed.time) as died,
 count(distinct killer.killed_cid, killer.time) - count(distinct killed.killer_cid, killed.time) as points
from kill_log as killer
left join kill_log as killed (killer.killer_cid = killed.killed_cid)
group by killer.killer_cid

answered Jun 29, 2012 at 17:15

Puggan Se

5,8462 gold badges25 silver badges51 bronze badges

2 Comments

Holger Brandt Over a year ago

Wouldn't this fail if the cid had no kills (but got killed)?

Puggan Se Over a year ago

true, but if it was for a top list, thats ok, and if not your and eggyal answar works

Collectives™ on Stack Overflow

mysql count with multiple groups

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related