1

Let's say I have the following tables :

Table 1

task | date 
100  | 2016-01-01
101  | 2016-01-01
103  | 2016-01-01

Table 2

task2 | date 
113   | 2016-01-01
113   | 2016-01-01
103   | 2016-01-01

I would like to get to this result:

task_result | Count
100         | 0
101         | 0
103         | 1

Basically count data included in task1 table1 present in task2 table2.

Any idea?

2

You can do this:

SELECT Table1.Task, COUNT(Table2.Task)
FROM Table1
LEFT JOIN Table2
    ON Table1.Task = Table2.Task
GROUP BY Table1.Task ;

Table1 is your base set, so join to it. You want to retain all values, so you use a LEFT JOIN and you join on the matching task values. The group and count operations happen on different columns - the GROUP BY is based on your tasks from Table1, and you COUNT the of tasks from Table2.

Here's a demo (using CTEs as implemented by SQL Server, PostgreSQL, Oracle, etc.):

WITH Table1 AS
(
    SELECT 100 AS Task
    UNION ALL
    SELECT 101
    UNION ALL
    SELECT 103
)
,Table2 AS
(
    SELECT 113 AS Task
    UNION ALL
    SELECT 113
    UNION ALL
    SELECT 103
)

SELECT Table1.Task, COUNT(Table2.Task)
FROM Table1
LEFT JOIN Table2
    ON Table1.Task = Table2.Task
GROUP BY Table1.Task ;

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.