• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

mySQL Count with Conditions

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
0
BenBlingBucks
Asked:
BenBlingBucks
  • 8
  • 4
  • 4
  • +2
1 Solution
 
johanntagleCommented:
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;
0
 
johanntagleCommented:
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;
0
 
BenBlingBucksAuthor 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
 
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Kevin CrossChief Technology OfficerCommented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
OP_ZaharinCommented:
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
0
 
BenBlingBucksAuthor 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
0
 
OP_ZaharinCommented:
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
0
 
OP_ZaharinCommented:
- 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
0
 
BenBlingBucksAuthor 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
0
 
OP_ZaharinCommented:
- 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
0
 
BenBlingBucksAuthor 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:   http://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
0
 
johanntagleCommented:
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

0
 
BenBlingBucksAuthor 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.
0
 
SharathData EngineerCommented:
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.
0
 
BenBlingBucksAuthor 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

0
 
johanntagleCommented:
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
0
 
SharathData EngineerCommented:
>> 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

0
 
BenBlingBucksAuthor 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
0
 
SharathData EngineerCommented:
try this.
SELECT COUNT(*)
  FROM products p
 WHERE p.available = '1'
   AND EXISTS (SELECT 1 FROM inventory i WHERE p.id = i.product_id HAVING SUM(quantity) > 0)

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
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

0
 
BenBlingBucksAuthor Commented:
Sharath_123 hit it on the nose!  Thanks!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now