Mysql count id displayed in 2 columns

Question

I have a table with these columns:

• ID
• user_id
• player_in
• player_out
• the date

I need to make a report that counts the number of repetitions of each "player" in both the player_in field and the player_out field. For example, if I have two rows in a table (in the appropriate order).

id user_id player_in player_out 1 1 88 56 2 7 77 88 

The result for player 88 will be 2, and for players 56 and 77 only 1

Answer 1

Use a subquery that uses union all to get two columns into one column, then use the standard count(*) :

Note. Thus, the request included separate totals for inputs and outputs in accordance with a further request in the comments to this answer.

 select player_id, count(*) as total, sum(ins) as ins, sum(outs) as outs from ( select player_in as player_id, 1 as ins, 0 as outs from mytable union all select player_out, 0, 1 from mytable ) x group by player_id 

Note: you should use union all (not just union ) because union removes duplicates, while union all does not work.

Answer 2

You can use cross join with a 2-row virtual table to disable player_* columns and then group the results, for example:

 SELECT player, COUNT(*) AS total_count FROM ( SELECT CASE WHEN x.is_in THEN t.player_in ELSE t.player_out END AS player FROM mytable t CROSS JOIN (SELECT TRUE AS is_in UNION ALL SELECT FALSE) x ) s GROUP BY player ; 

Thus, each row of the source table is essentially duplicated, and each copy of the row contains either player_in or player_out , depending on whether the derived table column is is_in TRUE or FALSE to form a single player column. This promotion method may work better than the UNION method proposed by @Bohemian , because this method (physical) table is only transmitted once (but you will need to test and compare both methods to determine if there is substantial benefit for this approach in your particular situations).

To count the number of incoming and outgoing numbers, as you requested in one of your comments on the above answer, you can expand your original sentence as follows:

 SELECT player, COUNT( is_in OR NULL) AS in_count, COUNT(NOT is_in OR NULL) AS out_count, COUNT(*) AS total_count FROM ( SELECT x.is_in, CASE WHEN x.is_in THEN t.player_in ELSE t.player_out END AS player FROM mytable t CROSS JOIN (SELECT TRUE AS is_in UNION ALL SELECT FALSE) x ) s GROUP BY player ; 

As you can see, the view now additionally returns the is_in column by itself, and the column is used in two conditional aggregates to calculate how many times a player has been in and out. (If you're interested, the OR NULL trick is explained here .)

You can also rewrite COUNT(condition OR NULL) records as SUM(condition) . This will certainly cut down on both expressions, some will also find a way to SUM count clearer / more elegant. In any case, there probably will not be any difference in performance, so choose the method that best suits your taste.

A demo version of SQL Fiddle of the second query can be found here .