CODEWITHSUNDEEP
sqlsqlite
Isaac Byrne
Isaac Byrne

Reputation: 613

SQL: Get Count of Strings Occurence in 2 Tables

I have two tables, each contain a column called symbol.

I want to query both tables and get a list containing each symbol and the number of times it occurred in each table like the example below:

Symbol | Count
a | 2
b | 6
c | 7

Below is what I currently have in terms of the query but I am not getting the result I want.

SELECT one.symbol, COUNT(*) FROM one
JOIN two GROUP BY one.symbol,two.symbol;

This ends up listing all of the symbols multiple times, and the count is very large.

I have also tried:

SELECT orders.symbol, COUNT(orders.symbol) FROM orders
JOIN executions GROUP BY orders.symbol;

This lists each symbol once like I want, however it only shows the count from one table twice (example count is 1, and it shows 11)

Upvotes: 1

Views: 35

Answers (1)

Alan
Alan

Reputation: 1428

Treat the two tables as one table by combining them in an inner subquery, then sum the counts outside...

SELECT 
    symbol,
    SUM(symbol_count)
FROM (
    SELECT
        symbol,
        COUNT(symbol) as symbol_count
    FROM
        table_one
    GROUP BY
        symbol

    UNION ALL

    SELECT
        symbol,
        COUNT(symbol) as symbol_count
    FROM
        table_two
    GROUP BY
        symbol
) AS symbol_counts
GROUP BY
    symbol

UPDATE: To answer the question posed in the comments, to get the counts side by side:

SELECT
    symbol,
    COALESCE(table_one.symbol_count, 0) AS table_one_count,
    COALESCE(table_two.symbol_count, 0) AS table_two_count
FROM
    (SELECT
        symbol,
        COUNT(symbol) as symbol_count
     FROM
        table_one
     GROUP BY
        symbol) AS table_one
   FULL JOIN
    (SELECT
        symbol,
        COUNT(symbol) as symbol_count
     FROM
        table_two
     GROUP BY
        symbol) AS table_two
   ON table_one.symbol = table_two.symbol

FULL JOIN ensures that if either table is missing one of the symbols it is still picked up. COALESCE will ensure that if one of them is missing (returns NULL, it will be replaced with a zero.

Upvotes: 1

Related Questions