CODEWITHSUNDEEP
mysqlselectcount
Joe Guson Noh
Joe Guson Noh

Reputation: 23

How to count specific value from multiple columns which references value from another table

I have two tables looking like below.

Table history :

+------+------+------+-----+------+------+
| fir  | sec  | thi  | fou | fif  | six  |
+------+------+------+-----+------+------+
|   10 |   23 |   29 |  33 |   37 |   40 |
|    9 |   13 |   21 |  25 |   32 |   42 |
|   11 |   16 |   19 |  21 |   27 |   31 |
|   14 |   27 |   30 |  31 |   40 |   42 |
|   16 |   24 |   29 |  40 |   41 |   42 |
|   14 |   15 |   26 |  27 |   40 |   42 |
|    2 |    9 |   16 |  25 |   26 |   40 |
|    8 |   19 |   25 |  34 |   37 |   39 |
|    2 |    4 |   16 |  17 |   36 |   39 |
|    9 |   25 |   30 |  33 |   41 |   44 |
|    1 |    7 |   36 |  37 |   41 |   42 |
|    2 |   11 |   21 |  25 |   39 |   45 |
|   22 |   23 |   25 |  37 |   38 |   42 |
|    2 |    6 |   12 |  31 |   33 |   40 |
|    3 |    4 |   16 |  30 |   31 |   37 |

Table stats:

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |

So table history has 6 columns. All 6 columns have different values and can be filled with values from 1 to 45. Table stats has id column from 1 to 45.

What i want is count "id" values from table history and have another column as count as a result.

I have tried

select id from stats as num right join (select count(*) as count from history where fir = num.id OR sec = num.id OR thi = num.id OR fou = num.id OR fif = num.id OR six = num.id) as r

but didn't work.

Can i have the result as i want with one query?

Upvotes: 0

Views: 41

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522396

You could try a left join between the "calendar" stats table and a union of your current data.

SELECT
    s.id,
    COUNT(t.val) AS count
FROM stats s
LEFT JOIN
(
    SELECT fir AS val FROM history UNION ALL
    SELECT sec FROM history        UNION ALL
    SELECT thi FROM history        UNION ALL
    SELECT fou FROM history        UNION ALL
    SELECT fif FROM history        UNION ALL
    SELECT six FROM history
) t
    ON s.id = t.val
GROUP BY
    s.id;

Note that here we bring all six columns into a single logical column before doing the overlap check with stats. That we need to do this union implies that maybe your table design should actually just have one logical column, with a second column which maintains the data source for each point.

Demo

Upvotes: 2

Ged Clack
Ged Clack

Reputation: 1

Yeah, just an idea but you can use UNION, but maybe there is better solution

SELECT s.ID, COUNT(his.x) AS Counted
FROM stats s
JOIN
(SELECT fir AS x FROM history
UNION
SELECT sec AS x FROM history
UNION
SELECT thi AS x FROM history
UNION
SELECT fou AS x FROM history
UNION
SELECT fif AS x FROM history
UNION
SELECT six AS x FROM history) AS his ON s.ID=his.x
GROUP BY s.ID

Upvotes: 0

Related Questions