CODEWITHSUNDEEP
mysqlsubquery
Judson Cooper
Judson Cooper

Reputation: 51

Getting Mysql results where subquery is not null

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

https://www.db-fiddle.com/f/mHuQ4nCHZVPmEacog1iQvq/0

Upvotes: 0

Views: 197

Answers (3)

P L
P L

Reputation: 114

SELECT IFNULL(SUM(qty),0) ....

Upvotes: 1

18Man
18Man

Reputation: 572

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

Upvotes: 1

Arganas
Arganas

Reputation: 73

I'm no SQL expert by any strech, but it seems as if this should work.

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 
shipped is not Null and 
unshipped is not null

Upvotes: 0

Related Questions