-1

I have two tables, let's call them Table1 and Table2. Table1 has a column c1. and Table2 has two columns called c2 and c3.

I would like to count the occurrences of c1 from Table1 in both columns of Table2.

SELECT DISTINCT Table1.c1, count(*) FROM Table1
WHERE Table1.c1 IN(SELECT c2 OR c3 FROM Table2)
GROUP BY NAME;

This apparently did not work for me, I am trying to practice different operations in SQL but I couldn't figure this one out.

1
  • Do not mix DISTINCT and GROUP BY. Since you are using an "aggregate function" (COUNT(*)), use GROUP BY.
  • The list in the GROUP BY (NAME) should match the non-aggregate columns in the SELECT (Table1.c1)
  • OR is a boolean operator, such as testing whether either of two things is true. It cannot be used for collecting things.
  • UNION (see below) can collect stuff from two tables (or 2 different columns in the same table, in your case).
  • Try to avoid IN ( SELECT ... ); learn about JOIN.

The union:

(
    SELECT c2 FROM Table2
    UNION ALL
    SELECT c3 FROM Table2
)

effectively build a temporary table with one column, called c2 (after the name(s) in the first SELECT).

Use UNION DISTINCT if you need to de-duplicate the results.

Now, Abdul, take these bits of knowledge and try again.

0

You have to JOIN both tables. For mysql v8+ you can use CTE syntax:

WITH t1 AS 
( SELECT DISTINCT c1 
    FROM table1
) 
SELECT t1.c1
     , count(1) AS counter
  FROM t1
  JOIN table2 AS t2  ON t2.c2 = t1.c1
                     OR t2.c3 = t1.c1
 GROUP BY t1.c1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.