We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

mySQL Count with Conditions

Medium Priority
425 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Top Expert 2012

Commented:
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;
Top Expert 2012

Commented:
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;

Author

Commented:
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
 
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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;

Open in new window


HTH
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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.
Top Expert 2011

Commented:
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

Author

Commented:
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
Top Expert 2011

Commented:
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
Top Expert 2011

Commented:
- 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

Author

Commented:
'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
Top Expert 2011

Commented:
- 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

Author

Commented:
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/Programming/Languages/SQL_Syntax/Q_26972056.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
Top Expert 2012

Commented:
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;

Open in new window

Author

Commented:
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.
SharathData Engineer
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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

Top Expert 2012

Commented:
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
SharathData Engineer
CERTIFIED EXPERT

Commented:
>> 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)

Open in new window

Author

Commented:
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
Data Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

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

Open in new window

Author

Commented:
Sharath_123 hit it on the nose!  Thanks!
Unlock the solution to this question.
Thanks for using Experts Exchange.

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.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.