4

I have two tables in a database of different product brands that I'm trying to get a count of so that it combines the count of both given the description search. What I have so far is this:

$pages_query = mysql_query ("SELECT COUNT('Product Number') FROM brand1 WHERE description LIKE '%oven%' UNION ALL SELECT COUNT('Product Number') FROM brand2 WHERE description LIKE '%oven%'") ;

When I echo this, it spits out 60, which is the result for brand1 table only and does not add the count from the brand2 table (which I thought UNION ALL was supposed to do or do I have that incorrect?) The end number should be 173 because the other table has 113 results that match this query.

Which would be the best way to add the count results into one number?

Thanks.

Improve this question
2
  • 1
    Michael, Note the info at the bottom of Michael Berkowski's answer below. You should combine your products into a single table with brand as a column. Your query would then be SELECT COUNT(*) FROM products WHERE description LIKE '%oven%' AND brand IN ('brand1', 'brand2'); - much simpler and more elegant solution Commented Dec 3, 2012 at 0:33
  • By the way, welcome to Stack Overflow! Commented Dec 3, 2012 at 0:35

2 Answers 2

Reset to default
3

A UNION should give you two rows, one for each separate count. If you want the total of the two, you can add them in a subselect:

SELECT
  /* Add these two counts together and alias as total_count */
  (SELECT COUNT(*) FROM brand1 WHERE description LIKE '%oven%') 
  + (SELECT COUNT(*) FROM brand2 WHERE description LIKE '%oven%) AS total_count

MySQL is lenient about the contents or presence of a FROM clause, so you can omit it and use only the SELECT.

As a side note, it is not a typical database practice to have separate tables per thing like a brand. Instead, you should probably have these combined into a single table, which has a column identifying the brand. It is likely to simplify your life greatly down the line, if you are in a position to make such a change now.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Beat you by a couple seconds, but I like your solution, so up-vote for you.
Thanks but I was actually a couple seconds before you. Sort by oldest :) +1 for yours also anyway.
+1 The cleanest way in this situation. In other cases you may prefer a sum, like @TheSmose suggested.
@MichaelBerkowski Aha, you're right! :) Cheers for an elegant solution.
@TheSmose Thanks for your wonderful help. I'll make the recommended changes to my database. Thanks for the warm welcome as well. I hope some day I can helpful contributions too!
|
2

When you UNION, you're creating a row for each count. You have to get a SUM of those two rows.

By the way, you were selecting COUNT('Product Number') - which is for all intents and purposes the same as COUNT(1). If you want to check a particular column for a non-null value, you should name that column. If Product Number is the name of your column, you should encapsulate it in back-ticks (`). Regular single-quotes (') will merely define a string.

$sql = "SELECT SUM(rowcount)
        FROM (
            SELECT COUNT(1) AS rowcount 
            FROM brand1 
            WHERE description LIKE '%oven%' 
            UNION ALL 
            SELECT COUNT(1) 
            FROM brand2 
            WHERE description LIKE '%oven%'
        ) AS counts";
$pages_query = mysql_query ($sql) ;
Improve this answer

3 Comments

Thanks a million. I see what you've done here, and it works perfectly. Many thanks.
A working solution (so +1), but I would prefer @MichaelBerkowski's answer. Here you aggregate two times.
+1 - I like @MichaelBerkowski's answer better, too. Leaving mine here for the column name info.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.