Merge two tables with count's and sum's into one result

Question

I have this two queries:

SELECT 
    b.ref,
    ts.id_baits,
    SUM(strftime('%s',ts.endtime) -strftime('%s',ts.initime)) AS t,COUNT(*) AS n 
FROM TimeSegments AS ts
INNER JOIN Baits AS b ON b.id = ts.Id_Baits
GROUP BY  ts.id_baits
ORDER BY b.ref


SELECT  b.ref,
    COUNT(CASE WHEN e.Status=1 THEN 1 END) AS Undefined,
    COUNT(CASE WHEN e.Status=1 THEN 1 END) AS GetClose,
    COUNT(CASE WHEN e.Status=2 THEN 1 END) AS Threat,
    COUNT(CASE WHEN e.Status=3 THEN 1 END) AS Attack,
    COUNT(CASE WHEN e.Status=4 THEN 1 END) AS Hooked,
    COUNT(CASE WHEN e.Status=5 THEN 1 END) AS Captured,
    COUNT(CASE WHEN e.Status=6 THEN 1 END) AS Tagged,
    COUNT (*) AS TOTAL
FROM CastingsEvents AS e
LEFT JOIN Trajectories AS tr ON tr.id = e.id_trajectories 
LEFT JOIN TimeSegments AS ts ON ts.id = tr.id_timesegments
LEFT JOIN Baits AS b  ON b.id = ts.Id_Baits
GROUP BY  ts.id_baits
ORDER BY b.ref

As seen, both tables are grouped by id_baits.

I want to merge the results into one table, does someone know how to do it?

I think this might help [UNION Clause](https://www.tutorialspoint.com/sqlite/sqlite_unions_clause.htm) — dotGitignore, Oct 11 '17 at 02:27
What exactly do you mean with "merge"? Please provide some sample data and your desired output. (See [How to format SQL tables in a Stack Overflow post?](https://meta.stackexchange.com/q/96125) for how to add some.) — CL., Oct 11 '17 at 07:07
Hello Jerrol, the UNION clause needs both tables have the same columns (correct?), this is not the case. — sgm, Oct 11 '17 at 08:30
Hello CL., I just want to add the columns of Table2 to the columns of Table1, as said, both are grouped by the same column. — sgm, Oct 11 '17 at 08:40

score 0 · Answer 1 · answered Oct 11 '17 at 09:23

As proposed (JOIN two SELECT statement results), a LEFT JOIN solve the problem:

SELECT * FROM (
    SELECT
        b.ref AS bait,
        ts.id_baits,
        SUM(strftime('%s',ts.endtime) -strftime('%s',ts.initime)) AS t,
        COUNT(*) AS n 
    FROM TimeSegments as ts
    INNER JOIN Baits as b ON b.id = ts.id_baits
    GROUP BY  ts.id_baits
) BaitsUsage 
LEFT JOIN (
    SELECT  
        ts.id_baits,
        COUNT(CASE WHEN e.Status=1 THEN 1 END) AS Undefined,
        COUNT(CASE WHEN e.Status=1 THEN 1 END) AS GetClose,
        COUNT(CASE WHEN e.Status=2 THEN 1 END) AS Threat,
        COUNT(CASE WHEN e.Status=3 THEN 1 END) AS Attack,
        COUNT(CASE WHEN e.Status=4 THEN 1 END) AS Hooked,
        COUNT(CASE WHEN e.Status=5 THEN 1 END) AS Captured,
        COUNT(CASE WHEN e.Status=6 THEN 1 END) AS Tagged,
        COUNT (*) AS TOTAL
    FROM CastingsEvents AS e
    LEFT JOIN Trajectories AS tr ON tr.id = e.id_trajectories 
    LEFT JOIN TimeSegments AS ts ON ts.id = tr.id_timesegments
    LEFT JOIN Baits AS b  ON b.id = ts.Id_Baits
    GROUP BY ts.id_baits
) BaitsEvents
ON BaitsUsage.id_baits=BaitsEvents.id_baits
ORDER BY bait

Merge two tables with count's and sum's into one result

1 Answers1