MySQL distinct values across columns

Question

I have the following table:

id    lb     rb     ls     rs     ch     bk     ot
 1  10000  10001  10001  10001  10001  10001  10000
 2      0  10000      0  10001      0  10000      0
 3      0      0  10000  10001  10000      0      0
 4      0      0      0  10000      0      0      0
 5      0      0      0  10000      0      0      0

I want to be able to get the total distinct values across all columns (excluding 0) so the result is as such:

Code   Qty
10000    8
10001    7

What's the easiest/best way to do this?

Thanks, Stu

ypercubeᵀᴹ · Accepted Answer · 2011-09-28 14:28:59Z

1

You can do

SELECT col1, COUNT(*)
FROM
  (
  SELECT lb AS col1
  FROM table
    UNION ALL 
  SELECT rb
  FROM table
    UNION ALL ... etc
  ) a
WHERE col1 != 0
GROUP BY col1

Instead of WHERE col1 != 0 you can add WHERE field_name !=0 to each SELECT in UNION

edited Sep 28, 2011 at 14:28

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

answered Sep 28, 2011 at 13:55

a1ex07

37.4k12 gold badges94 silver badges104 bronze badges

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

2 Comments

Stu Ayton Over a year ago

I'm afraid that's throwing the error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') WHERE col1 != 0 GROUP BY col1'

JNDPNT Over a year ago

I think it might have something to do with the ")a". I don't really understand why the a... Try removing it. Id this still doesn't work you can show your full query?

ypercubeᵀᴹ · Accepted Answer · 2011-09-28 14:20:34Z

0

SELECT code     AS Code
     , SUM(cnt) AS Qty
FROM
  (   SELECT lb AS code
           , COUNT(*) AS cnt
      FROM TableX
      GROUP BY lb        
  UNION ALL
      SELECT rb AS code
           , COUNT(*) AS cnt
      FROM TableX
      GROUP BY rb
  UNION ALL        
    ...
  UNION ALL
      SELECT ot AS code
           , COUNT(*) AS cnt
      FROM TableX
      GROUP BY ot
  ) AS tmp
GROUP BY code

edited Sep 28, 2011 at 14:20

answered Sep 28, 2011 at 14:10

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

2 Comments

Stu Ayton Over a year ago

Thanks, but I'm getting Every derived table must have its own alias error with this :(

ypercubeᵀᴹ Over a year ago

Try now. I've added parenthesis in the correct place. The only thing I haven't put is the WHERE col <> 0

Collectives™ on Stack Overflow

MySQL distinct values across columns

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