mySQL Count with Conditions

TRY:

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;

or to be safe from null quantities:

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))>0
order by 2 desc;

Thank you but that query won't work because 'quantity' field does not exist in the 'products' table.

There's a separate 'inventory' table that can have multiple instances of the same product id, like:
product_id | quantity
------------------------
12345 | 2
12345 | 1
12345 | 4

So the sum for p.id would be 7 for the 'id' in 'products' table.  That's why I used this additional SELECT entry while searching through 'products' table.
     IFNULL((SELECT SUM(quantity) FROM inventory i WHERE p.id = i.product_id), 0)

Thanks... looking forward.

Ben
 

Using a HAVING as shown previously should work, but remember you can use the alias quantity in the HAVING clause as the SELECT statement has not been evaluated yet by that point.  You will need to repeat the aggregate as shown by the previous expert.

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;

Select allOpen in new window

HTH

Basically, it is what was posted here -- http:#a35447870 -- I would expect it to be more like that really, if you are wanting quantity by product.  As written above, you would get count and sum overall if you have more than one product tagged as available = 1.

SELECT 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

Thank you for your input mwvisa1, however your query will only return the total amount of records in the 'inventory' table as the result for COUNT, and the total inventory count for all products for the result 'quantity'

Let me try to explain it a little more:

I need to come up with a total COUNT # of all product ids in 'products' table coupled with a sum of  their 'quantity' data (can have several quantity entries for same product id because more items arrive at different times) in the 'inventory' table with the common reference as with the p.id = i.product_id.

Everything seems simple so far... but I only need a COUNT value of what's in stock, so I added HAVING quantity > 0 BUT it returns no value.   For some reason, the query doesn't identify each product id with it's own quantity value.  If I add GROUP BY p.id, then it returns all the correct items with their summed quantity, but I only want a COUNT #.

If this is not solvable, then how can I count the # of records it displays if I have to use the GROUP BY method?

Thanks again.
Ben

Hope that helps.

Ben

ben,
- 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

- try this:

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

'products' table
id | name | available
1 | item1 | 1
2 | item2 | 1
3 | item3 | 1

'inventory' table
id | product_id | quantity
1 | 1 | 3
2 | 2 | 2
3 | 1 | 1
4 | 1 | 2
5 | 2 | 5

In it's process, it should find that:
  - product id 1 with a sum quantity of 6
  - product id 2 with a sum quantity of 7
  - product id 3 will default to a sum quantity of 0 because it is not found in the 'inventory' table
     hence why I used:  IFNULL((SELECT SUM(quantity) FROM inventory i WHERE p.id = i.product_id), 0)

The COUNT value should return a value of 2, because there's only 2 products that have an inventory 'quantity' sum over zero.

Get it?  Seems simple, but for some reason I can't get a COUNT value.

Thanks again everyone!

Ben

- ok get it :)

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

Hello OP,

Your query returns the same full results (all records) as my original query along with my comments about adding the GROUP BY:   https://www.experts-exchange.com/questions/26972056/mySQL-Count-with-Conditions.html#35448801

My new query with that comment would've been:

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

What I've been seeking for is not display all records, I only need 1 COUNT # value.  Just like how you would get if you did something like:  SELECT COUNT(*) FROM products... which would return 1 COUNT # value.

Almost there. :)
Ben

Hi Ben,

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;

Select allOpen in new window

I appreciate everyone's help on the matter, however none of the suggested scripts here returns the one total value that I was seeking, instead they return all the records.

For now, I am running a loop to count the total of items... not the preferred way, but works.

Thanks.

For the sample set given in http:#35449091 can you provide the expected result in tabular format? If you don't want any further assistance here, please ignore my comment.

I'm looking for one value like what you would get from COUNT(*) - I only need one value.  The catch is that I need to retrieve a SUM for each item ID in the inventory table to GROUP the item IDs

The inventory table can have multiple values for one item ID.  Like:
'inventory' table
id | product_id | quantity
1 | 1 | 3
2 | 2 | 2
3 | 1 | 1
4 | 1 | 2
5 | 2 | 5

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

:)  Thanks.

Ben

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

>> The idea is to get a COUNT value for the total number of items that has a positive value for quantity (in inventory).
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 allOpen in new window

Nice try, but no cigar. :)  using the word 'quantity' in an AS relation was probably a bad idea, when 'quantity' is also a field name.  Think of all the 'AS quantity'  to  'AS instock'

The inventory table can have multiple instances of an item when they are sold or replenished and each time inserts a record of such... for example:

(I took out the auto increment id field to avoid confusion)
product_id | quantity
       1         |      3
       2         |      2
       3         |      1
       1         |      1
       1         |     -2
       2         |      5
       3         |     -1
       1         |     -1

The sum of product_id 1 would = 1... would be included in COUNT = 1
The sum of product_id 2 would = 7... would be included in COUNT = 2
The sum of product_id 3 would = 0... would not be included in COUNT, value is still = 2

I want a singular COUNT value of the amount of items that have 'instock' > 0

Get it?

Many thanks.
Ben

Here is an alternative, just to add to your library in case in the future you want to see the products and their respective balance on hand values.  You can check execution plan for which performs best in your scenario, but looks like Sharath got what you needed above at first glance.

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'
;

Select allOpen in new window

Sharath_123 hit it on the nose!  Thanks!