MySQL – Counting on Two Different Columns in the Same Table

MySQL

I run a pool league and have kept stats in Excel. I want to automate via a website and MySQL will be my primary data analysis tool, though my knowledge there is only at the beginning of a long journey.

I have 2 tables (only showing relevant tables and relevant fields)

users:
--------------------
id            BigInt
display_name  VarChar(250)


match_results:
---------------------
id            BigInt
winner_id     BigInt
loser_id      BigInt

I want to produce a result set to show as a table of standings, like this:

NAME          WINS     LOSSES
-------------------------------
User2dn        12         7
User1dn        11         8
User5dn        10         8
etc.

I can manage the ORDER BY

I have other queries where I have successfully joined these two tables to get the display names for both winner_id and loser_id by using 2 different aliases for the same user table and that worked great, but I'm totally baffled here.

What I really need is more complicated than this, since I need to report from multiple other columns in match_results, the max of high_run (as a winner or loser) and bonus points which will be a sum irrespective of winner or loser status. I wanted to keep this question as simple as I could so I could understand it, and then build up the additional complications later. Hope that's OK.

Best Answer

John,

If you have a table of users, and a table of matches, you should probably have a reference table between the two of them, that has a many-to-many relationship.

users:
--------------------
id (PK)       Int
display_name  VarChar(250)

matches:
---------------------
matchId (PK)   Int
...
...
  -- Other relevant attributes about the match

results:
---------------------
ResultId (PK)      Int
Name           Varchar(100) 
  -- Win, loss, whatever else

match_results:
---------------------
matchId (PK, FK)  Int
UserId (PK, FK)   Int
ResultId (PK, FK) Int

For every match, you should have a record for every participant in that match, and their relevant result. Unless you plan on having more than 2 billion matches or users, I would stick to using Int.

Now you can join across these tables and SUM() or MAX() or whatever aggregate you would prefer.

SELECT u.Name , 
   COUNT( CASE
          WHEN r.Name = 'Win' THEN 1
          END )AS Wins , 
   COUNT( CASE
          WHEN r.Name = 'Loss' THEN 1
          END )AS Losses
FROM match_results AS mr INNER JOIN users AS u ON mr.userId = u.id
                       INNER JOIN matches AS m ON mr.matchId = m.id
                       INNER JOIN results AS r ON mr.resultId = r.id;

Edit:

Using your existing schema, you can use sub queries to aggregate your statistics

  SELECT u.display_name AS NAME, 
    (SELECT COUNT(1) FROM match_results mr WHERE mr.winner_id = u.id) AS Wins,
    (SELECT COUNT(1) FROM match_results mr WHERE mr.loser_id = u.id) AS Losses
  FROM users u

PROPOSED QUERY

SELECT
    IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Country,Hour WITH ROLLUP;

YOUR SAMPLE DATA

USE test
DROP TABLE IF EXISTS hits;
CREATE TABLE hits
(
    id int not null auto_increment,
    time datetime,
    country varchar(20),
    PRIMARY KEY (id)
);
INSERT INTO hits (time,Country) VALUES
('2014-06-19 01:00:00','France'),
('2014-06-19 01:00:00','Brazil'),
('2014-06-19 01:00:00','USA'),
('2014-06-19 02:00:00','USA');

YOUR SAMPLE DATA LOADED

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS hits;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE hits
    -> (
    ->     id int not null auto_increment,
    ->     time datetime,
    ->     country varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO hits (time,Country) VALUES
    -> ('2014-06-19 01:00:00','France'),
    -> ('2014-06-19 01:00:00','Brazil'),
    -> ('2014-06-19 01:00:00','USA'),
    -> ('2014-06-19 02:00:00','USA');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM hits;
+----+---------------------+---------+
| id | time                | country |
+----+---------------------+---------+
|  1 | 2014-06-19 01:00:00 | France  |
|  2 | 2014-06-19 01:00:00 | Brazil  |
|  3 | 2014-06-19 01:00:00 | USA     |
|  4 | 2014-06-19 02:00:00 | USA     |
+----+---------------------+---------+
4 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT
    ->     IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
    ->     COUNT(1) TotalHits
    -> FROM
    -> (
    ->     SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    ->     FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
    -> ) AA
    -> GROUP BY Country,Hour WITH ROLLUP;
+-------------------------+-----------+
| Statistic               | TotalHits |
+-------------------------+-----------+
| 2014-06-19 01:00:00     |         1 |
| Total for Brazil        |         1 |
| 2014-06-19 01:00:00     |         1 |
| Total for France        |         1 |
| 2014-06-19 01:00:00     |         1 |
| 2014-06-19 02:00:00     |         1 |
| Total for USA           |         2 |
| Total for All Countries |         4 |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql>

I'll leave the formatting to you :-)

CAVEAT: Please make sure you have a time index on the table

ALTER TABLE hits ADD INDEX time_index (time);

An additional query could be

SELECT
    IFNULL(Country,CONCAT('Total for ',IFNULL(Hour,DATE(NOW())))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Hour,Country WITH ROLLUP;

whose output would be

+-------------------------------+-----------+
| Statistic                     | TotalHits |
+-------------------------------+-----------+
| Brazil                        |         1 |
| France                        |         1 |
| USA                           |         1 |
| Total for 2014-06-19 01:00:00 |         3 |
| USA                           |         1 |
| Total for 2014-06-19 02:00:00 |         1 |
| Total for 2014-06-19          |         4 |
+-------------------------------+-----------+
7 rows in set (0.00 sec)

UPDATE 2014-06-21 19:07 EDT

Perhaps the the two queries combined with UNION will work for you

SELECT
    IFNULL(Country,CONCAT('Total for ',IFNULL(Hour,DATE(NOW())))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Hour,Country
UNION
SELECT
    IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Country,Hour WITH ROLLUP;

I removed the WITH ROLLUP from the first one so that the total comes out once

Best Answer

Related Solutions

Mysql – How to join two result sets from same table with different conditions on same column

MySQL – How to GROUP BY Two Different Columns