Count(*)

[halifaxer]

need some help with the following statement. i'm trying to use it to detect from my database if there is more than ONE row where sku='$sku' AND inStock='yes' ie.

ROW SKU inStock
1 1234 yes
2 1234 yes
3 1234 no

In this case it will return rows 1 & 2, but not 3 as it is listed as not in stock.

SELECT sku,inStock,COUNT(*) as total FROM products WHERE sku='$sku' AND inStock='yes' GROUP BY sku,inStock HAVING total > 1 ORDER BY search, save

Am I close?

[Fumigator]

Just in case the HAVING clause doesn't let you use a column in the select, then you can use COUNT(*) and that will definitely work. And, I'm not really sure what the point of your ORDER BY is, that won't do anything for you.

[Old Pedant]

Excuse me, but what is the POINT in returning the inStock field when you *KNOW* that the value is going to be "yes", because that's what is in you WHERE clause???

For that matter, what's the point in returning the sku field? Again, you are only going to get one value there.

So:

SELECT COUNT(*) as total 
FROM products WHERE sku='$sku' AND inStock='yes' 
HAVING COUNT(*) > 1

No need for any GROUP BY, because there *IS* only one group.

And as Fumigator said, your ORDER BY clause makes no sense. The only field you COULD do ORDER BY is the sku field, but since this query will return only ZERO or ONE records, there's no point in any ORDER BY, at all.

Also, my personal opinion is that you should get rid of the HAVING clause.

Reason: It's going to be easier to know that you will always have one record, and then use your PHP code to check the COUNT() value. Simpler than doing the EOF check, really.

So I would just do

SELECT COUNT(*) as total 
FROM products WHERE sku='$sku' AND inStock='yes'

And then you can just skip results of zero and one in your PHP code.