How to find the count of a particular number that can exist in 2 columns

Question

I have a table that consist of a table that describes calls. Hence there is a to column and a from column. The problem is that I want the total messages sent by each number, which can be from or to. Refer to the table above for visuals.

I want the final table to be somethng that shows A : 3 , B: 2 , C:1 and D:1.

How do u count the numbers in 2 columns and sum them up?

Answer 1

One solution would be to first UNION ALL two aggregate queries to gather the count of occurences of each value in the two different columns, and them sum the results in an outer query, like:

SELECT val, SUM(cnt) cnt
FROM (
    SELECT `from` val, COUNT(*) cnt FROM mytable GROUP BY `from`
    UNION ALL
    SELECT `to`, COUNT(*) FROM mytable GROUP BY `to`
) x
GROUP BY val

This demo on DB Fiddle with your sample data returns:

| val | cnt |
| --- | --- |
| A   | 3   |
| B   | 2   |
| C   | 1   |
| D   | 1   |
| E   | 1   |