Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: Count(*)

  1. #1
    New Coder
    Join Date
    Feb 2008
    Posts
    37
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Count(*)

    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.

    Code:
    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?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    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:
    Code:
    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
    Code:
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •