Geek asks and answers

Executing a query on 2 columns as if they were

There are 2 columns containing the same data type:

col_a  col_b ...
Bob    Tim   ..
John   Frank .
Bob    John
John   Tim
Bob    Bob
Frank  John

Now the query I want to do is similar to this (counting occurrences: (Bob, 3), (John, 2), ..):

SELECT col_a, count(*) AS total FROM table1 GROUP BY col_a

But instead of running it on col_a, I want to run it on col_a and col_b at the same time ((Bob, 4), (John, 4), ..)

Any help is appreciated.

edit: THANKS EVERYONE YOU ARE AMAZING.

Thanks again

+3
source share
3 answers
Select Z.name, Count(*) As Total
From    (
        Select col_a As name
        From total
        Union All
        Select col_b
        From total
        ) As Z
Group By Z.name
+5
source 
select Name, count(*) as Total
from (
    select col_a as Name from MyTable
    union all
    select col_b from MyTable
) a
group by Name
+4
source

Based on the column Bob Bob, I think you need to group by subqueries:

select idx, sum(count)
from (
    select col_a as idx, count(*) as count
    from table
    union all
    select col_b as idx, count(*) as count
    where col_a <> col_b -- avoid dups
    ) as counts
group by idx

or

select idx, count(*)
from (
    select col_a as idx
    from table
    union all
    select col_b as idx
    where col_a <> col_b -- avoid dups
    ) as counts
group by idx
+1
source

More articles:


All Articles