Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – MSQL Count multiple columns group by each column

Table

COlumnA ColumnB
KLAX KONT
KBUR KJFK
KSAN KBUR
KJFK KPHX

Count distinct columnA
Count distinct columnB

Return a table:
KLAX 1 0
KBUR 1 1
KSAN 1 0
KJFK 1 1
KONT 0 1
KPHX 0 1

I don’t think this can be done with 1 Select
I even tried temp tables. I just can not wrap my head around this one.
Any ideas?

Tags:

2

Answers


  1. 0

    It appears that you want to report for distinct counts of the labels across both columns, in a single column. If so, then we can try the following union approach:

    SELECT val, COUNT(*) AS cnt
FROM
(
    SELECT ColumnA FROM yourTable
    UNION ALL
    SELECT ColumnB FROM yourTable
) t
GROUP BY val;
    Login or Signup to reply.
  2. 0 
    WITH Items AS
(
    SELECT ColumnA As Item FROM MyTable
    UNION
    SELECT ColumnB FROM MyTable
)
SELECT Item
   , (SELECT COUNT(*) FROM MyTable WHERE ColumnA = Item) 
   , (SELECT COUNT(*) FROM MyTable WHERE ColumnB = Item) 
FROM Items

    See it work here:

    https://dbfiddle.uk/uHaP4yoq

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search