I am working on a query that will get the count of items using a sub query, where the result of both sub queries is not null.
I have this query
SELECT s.id, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND status = 'S') AS shipped, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND (status = 'A' OR status = 'B')) AS unshipped FROM `sales` s WHERE s.status = 'A' OR s.status = 'B'
Which produces this:
id | shipped | unshipped
1500 | NULL | 2
1501 | NULL | 1
1502 | NULL | 1
1503 | 1 | 2
1504 | 2 | NULL
1505 | 2 | 5
I only want results where shipped and unshipped have a positive value. I have tried several things, such as
SELECT s.id, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND status = 'S' HAVING SUM(qty) > 0) AS shipped, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND (status = 'A' OR status = 'B') HAVING SUM(qty) > 0) AS unshipped FROM `sales` s WHERE s.status = 'A' OR s.status = 'B'
And
SELECT s.id, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND status = 'S') AS shipped, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND (status = 'A' OR status = 'B')) AS unshipped FROM `sales` s WHERE s.status = 'A' OR s.status = 'B' AND (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND status = 'S') IS NOT NULL AND (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND (status = 'A' OR status = 'B')) IS NOT NULL
Both still return the same results.
I created a DB Fiddle, but it shows no results found, which I ran the same code through MySql and it returns all items
Based on @Arganas answer, you can use HAVING
SELECT s.id,
(
SELECT SUM(qty)
FROM salesItems
WHERE sale_id = s.id AND status = 'S'
) AS shipped,
(
SELECT SUM(qty)
FROM salesItems
WHERE sale_id = s.id AND (status = 'A' OR status = 'B')
) AS unshipped
FROM `sales` s
WHERE (s.status = 'A' OR s.status = 'B')
HAVING shipped is not Null and unshipped is not null
this is the fiddle https://www.db-fiddle.com/f/mHuQ4nCHZVPmEacog1iQvq/1