Mysql query to count from multiple columns

Question

I have sql query like that:

SELECT
   CONCAT(t1.a, t2.a, t2.b, t3.a, t4.a, t4.b) AS val
FROM
   t1 
   LEFT JOIN t2 ON ...
   LEFT JOIN t3 ON ...
   LEFT JOIN t4 ON ...

val output as: 0,1,0,1,2,1 OR 1,2,0,1,0,2,... (includes 0,1,2 in random order)

I want count total for each number, as: 0=>2, 1=>3, 2=>1, without PHP code

What exactly do you want to know? I don't know what to say more, I think it's totally cleared. I want to count display times on each of value: 0,1,2 — Phong Hoang
– Phong Hoang, Commented Feb 3, 2016 at 5:14

Joachim Isaksson · Accepted Answer · 2016-02-03 05:24:13Z

1

You could simply wrap your existing query in a subquery, and make an outer query that counts the occurrences;

SELECT 
  LENGTH(val) - LENGTH(REPLACE(val, '0', '')) zeros,
  LENGTH(val) - LENGTH(REPLACE(val, '1', '')) ones,
  LENGTH(val) - LENGTH(REPLACE(val, '2', '')) twos
FROM (
  SELECT
    CONCAT(t1.a, t2.a, t2.b, t3.a, t4.a, t4.b) AS val
  FROM
    t1 
    LEFT JOIN t2 ON ...
    LEFT JOIN t3 ON ...
    LEFT JOIN t4 ON ...
);

answered Feb 3, 2016 at 5:24

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

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

Comments

Sameer Mirji · Accepted Answer · 2016-02-03 05:17:07Z

0

You need to use UNION ALL for this.

SELECT tbl.col1, COUNT(tbl.col1)
FROM (
  SELECT t1.a as col1
    FROM t1
  UNION ALL
  SELECT t2.a as col1
    FROM t2
  UNION ALL
  SELECT t3.a as col1
    FROM t3
   ...
) tbl
GROUP BY tbl.col1;

SQLFIDDLE DEMO

answered Feb 3, 2016 at 5:17

Sameer Mirji

2,24518 silver badges29 bronze badges

1 Comment

Phong Hoang Over a year ago

It's a good solution, but I think Joachim's solution is better. Thanks anyway!

Collectives™ on Stack Overflow

Mysql query to count from multiple columns

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related