Hello,

I need to run a query that does a very simple thing but I am unable to figure out the best way to do it.

I only want to get results that have an inventory count of more than zero. The below query comes with nothing returned for some reason. If I placed a GROUP BY, it will give me all of the records when all I need is just a count for now.

SELECT COUNT(*), IFNULL((SELECT SUM(quantity) FROM inventory i WHERE p.id = i.product_id), 0) AS quantity

FROM products p WHERE p.available = '1' HAVING quantity > 0 ORDER BY quantity DESC;

Thanks for your help.

Ben

I need to run a query that does a very simple thing but I am unable to figure out the best way to do it.

I only want to get results that have an inventory count of more than zero. The below query comes with nothing returned for some reason. If I placed a GROUP BY, it will give me all of the records when all I need is just a count for now.

SELECT COUNT(*), IFNULL((SELECT SUM(quantity) FROM inventory i WHERE p.id = i.product_id), 0) AS quantity

FROM products p WHERE p.available = '1' HAVING quantity > 0 ORDER BY quantity DESC;

Thanks for your help.

Ben

select p.id, sum(i.quantity)

from products p, inventory i

where p.id=i.product_id

group by p.id

having sum(i.quantity)>0

order by 2 desc;

select p.id, sum(ifnull(i.quantity,0))

from products p, inventory i

where p.id=i.product_id

group by p.id

having sum(ifnull(i.quantity,0))>

order by 2 desc;

```
SELECT COUNT(*)
, SUM(i.quantity) AS quantity
FROM products p
JOIN inventory i ON i.product_id = p.id
WHERE p.available = '1'
HAVING SUM(i.quantity) > 0
ORDER BY quantity DESC;
```

HTH

FROM products p, inventory i

WHERE p.id = i.product_id

AND p.available = '1'

GROUP BY p.id

HAVING sum(i.quantity) > 0

ORDER BY i.quantity DESC

- can you list here a sample data for products and inventory table. then sample output of the product id count and sum of quantity data that you're trying to achieve? it'll be much clearer for us to assist you

SELECT p.id, count(p.id), sum(i.quantity)

FROM products p, inventory i

WHERE p.id = i.product_id

AND p.available = '1'

GROUP BY p.id

HAVING sum(i.quantity) > 0

ORDER BY i.quantity DESC

SELECT i.product_id, count(i.product_id), sum(i.quantity)

FROM inventory i, products p

WHERE i.product_id = p.id

AND p.available = '1'

GROUP BY i.product_id

HAVING sum(i.quantity) > 0

ORDER BY i.quantity DESC

Okay I get your problem now. It is because the product count you want is not related to the other counts. You should be able to do it by forcing a cartesian join. Try:

```
select p.id, ic.product_count, sum(ifnull(i.quantity,0))
from products p, inventory i,
(select count(distinct product_id) as product_count from inventory
where quantity is not null and quantity>0) ic
where p.id=i.product_id
group by p.id, ic.product_count
having sum(ifnull(i.quantity,0))>0
order by 3 desc;
```

The idea is to get a COUNT value for the total number of items that has a positive value for quantity (in inventory).

Hi Ben,

It would be best if you can provide sample expected output, because based on my understanding of the above statement I will just do:

select count(product_id) from inventory where quantity>0;

And I doubt that's what you mean =). Thanks.

Johann

Did you try this?

```
SELECT COUNT(*)
FROM products p
WHERE p.available = '1'
AND EXISTS (SELECT 1 FROM inventory i WHERE p.id = i.product_id AND quantity > 0)
```

```
SELECT COUNT(*) AS CntInStock
FROM products p
JOIN (
SELECT product_id, SUM(quantity) AS BOH
FROM inventory
GROUP BY product_id
HAVING SUM(quantity) > 0
) i ON i.product_id = p.id
WHERE p.available = '1'
;
```

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.