Imagine I have two tables:

Table A

Table B

I want to create a query that joins the tables by names and separates sum of sales by table. I additionally want to filter my query to remove string matches or partial matches in this case by certain names.

What I want is the following result

Table C:

I know I can do this with a query like the following:

SELECT SUM(A.sales) AS 'A Sales Sum', SUM(B.sales) AS 'B sales Sum' FROM A
JOIN B
ON B.names = A.Names
WHERE Names NOT LIKE '%Gregg%' OR NOT LIKE '%Kim%'

The problem with this is the WHERE clause doesn't seem to apply, or applies to the wrong table. Since the Names column doesn't exactly match between the two, what I think is happening is when they are joined 'ON B.names = A.Names', the extras from B are being excluded? When I flip things around though I get the same result, which is no filter being applied. The wrong result I am getting is the following:

Table D:

Clearly I have a syntax issue here since I'm pretty new to SQL. What am I missing? Thanks!

CodePudding user response：

I think you want a union approach here:

SELECT
    SUM(CASE WHEN src = 'A' THEN sales ELSE 0 END) AS "A Sales Sum",
    SUM(CASE WHEN src = 'B' THEN sales ELSE 0 END) AS "B Sales Sum"
FROM
(
    SELECT sales, 'A' AS src FROM A WHERE Names NOT IN ('Gregg', 'Kim')
    UNION ALL
    SELECT sales, 'B' FROM B WHERE Names NOT IN ('Gregg', 'Kim')

) t;

Here is a demo showing that the above query is working.

CodePudding user response：

You don't need a join or a union of the tables and you shouldn't do it.
Aggregate in each table separately and return the results with 2 subqueries:

SELECT 
  (SELECT SUM(Sales) FROM A WHERE Names NOT LIKE '%Gregg%' AND Names NOT LIKE '%Kim%') ASalesSum,
  (SELECT SUM(Sales) FROM B WHERE Names NOT LIKE '%Gregg%' AND Names NOT LIKE '%Kim%') BSalesSum