How to count values in two columns-CodePudding

If I have two columns (start, end) with the same set of values, how can I count them and output them like my desired output? The first column TIME are the distinct values, COUNT1 is the count in the START column and COUNT2 is the count for the END column.

Sample:

START        END
----------------------
afternoon    evening
evening      night
evening      night
afternoon    evening
night        morning
night        morning

Output:

TIME         COUNT1      COUNT2
-------------------------------
morning      0             2
afternoon    2             0
evening      2             2
night        2             2

CodePudding user response：

Ideally there should be some table which holds every time of day label which you want to appear in your report. Sans this, we can use an inline query for this purpose. We can also use two separate aggregation queries here.

WITH times AS (
    SELECT 'morning' AS time UNION ALL
    SELECT 'afternoon' UNION ALL
    SELECT 'evening' UNION ALL
    SELECT 'night'
)

SELECT t.time,
       COALESCE(s.cnt, 0) AS COUNT1,
       COALESCE(e.cnt, 0) AS COUNT2
FROM times t
LEFT JOIN
(
    SELECT START, COUNT(*) AS cnt
    FROM yourTable
    GROUP BY START
) s
    ON s.START = t.time
LEFT JOIN
(
    SELECT `END`, COUNT(*) AS cnt
    FROM yourTable
    GROUP BY `END`
) e
    ON e.`END` = t.time;