MySQL: How to COUNT the number of rows returned by a Complicated Query

Question

This feels like it should be really easy, but I just can't get it and I've tried everything.

How can I just get the NUMBER OF ROWS returned by this query:

(SELECT Alias1 . *, 
        Alias2 . * 
 FROM   Table1 AS Alias1 
        LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
 WHERE  Alias1.Col1 != 'blah1' 
        AND Alias1.Col2 LIKE 'blah2' 
        AND ( Alias1.Col3 LIKE 'blah3' 
               OR Alias1.Col3 LIKE 'blah4' ) 
        AND Alias1.Col4 = 'blah5' 
        AND Alias2.Col7 LIKE 'blah6' 
 ORDER  BY Alias1.Col6 DESC 
 LIMIT  50) 

UNION 

(SELECT Alias1 . *, 
        Alias2 . * 
 FROM   Table1 AS Alias1 
        LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
 WHERE  Alias1.Col1 != 'blah1' 
        AND Alias1.Col2 LIKE 'blah2' 
        AND ( Alias1.Col3 LIKE 'blah3' 
               OR Alias1.Col3 LIKE 'blah4' ) 
        AND Alias1.Col4 = 'blah5' 
        AND Alias2.Col5 LIKE 'blah6' 
 ORDER  BY Alias1.Col6 DESC 
 LIMIT  50) 

UNION 

(SELECT Alias1 . *, 
        Alias2 . * 
 FROM   Table1 AS Alias1 
        LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
 WHERE  Alias1.Col1 != 'blah1' 
        AND Alias1.Col2 LIKE 'blah2'
        AND ( Alias1.Col3 LIKE 'blah3' 
               OR Alias1.Col3 LIKE 'blah4' ) 
        AND Alias1.Col4 = 'blah5' 
 ORDER  BY Alias1.Col6 DESC 
 LIMIT  50)

I suppose I could just use PHP to execute the query and then do a mysql_num_rows on the results, but I want to do it directly with SQL because I heard that's faster as it saves a step.

Thanks!

EDIT:

The following is giving me error (#1060 - Duplicate column name 'Col1'):

   SELECT COUNT(*) FROM (
   (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2' 
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
            AND Alias2.Col7 LIKE 'blah6' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50) 

    UNION 

    (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2' 
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
            AND Alias2.Col5 LIKE 'blah6' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50) 

    UNION 

    (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2'
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50)
   ) a

The following is giving me error (#1064 - You have an error in your SQL syntax; ):

 SELECT COUNT(*) FROM (
   (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2' 
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
            AND Alias2.Col7 LIKE 'blah6' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50) a

    UNION 

    (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2' 
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
            AND Alias2.Col5 LIKE 'blah6' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50) b

    UNION 

    (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2'
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50) c
   ) z

Why does wrapping the entire query in parentheses (making it a subquery) and doing a SELECT COUNT(*) on it fail to work? Does it return an error message? — FrustratedWithFormsDesigner, Aug 22 '11 at 15:23
What happens when you wrap it in Select Count(*) FROM( Your Long query) AS CountThis. Do you get a specific error, or do you get the wrong count? — Kibbee, Aug 22 '11 at 15:23
You do not provide a good reason to not use `mysql_num_rows()`. It is there for a reason... use it. Why do you expect it to be slow? — Brad, Aug 22 '11 at 15:23
I tried using `Select Count(*) from query` and it worked absolutely fine. Can you post what you exactly tried executing and the error that you got while using count? — reggie, Aug 22 '11 at 15:24
@reggie: What do you mean by the "inner sub query"? Yes, I repeated the question and made it more specific because no one was able to answer it, and one particular answer was getting upvotes even though it did not work. — ProgrammerGirl, Aug 22 '11 at 15:34
@Programmer: Does your query without the count keyword work? — reggie, Aug 22 '11 at 15:36
@reggie: Yes, my original query EXACTLY as I posted it in my post works perfectly. — ProgrammerGirl, Aug 22 '11 at 15:39
@Programmer let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/2737/discussion-between-reggie-and-programmer) — reggie, Aug 22 '11 at 15:39
possible duplicate of [MySQL: How to COUNT the number of rows returned by a UNION Query](http://stackoverflow.com/questions/7149685/mysql-how-to-count-the-number-of-rows-returned-by-a-union-query) — hammar, Aug 22 '11 at 17:07
@Programmer - the crucial thing here was the *error message*. By not including it in your original question you made a number of people waste their time looking at your code and trying to figure out the problem. Once you posted the error it became obvious what the solution was. Please remember this, and include all relevant details in any further questions you ask on SO. — APC, Aug 22 '11 at 18:17
It is not acceptable to repeat "the question and made it more specific because no one was able to answer it,". Please edit your original question instead. — Kev, Aug 22 '11 at 18:37

score 4 · Answer 1 · answered Aug 22 '11 at 14:08

4

Wrap your entire query in parentheses (make it a subquery) and do a select count(*) from it:

select count(*) from (YOUR HUGE QUERY UNION YOUR HUGE QUERY) a

answered Aug 22 '11 at 14:08

Derek

21,828
7
53
61

The actual error is: #1248 - Every derived table must have its own alias – ProgrammerGirl Aug 22 '11 at 14:13
1

try putting an alias after each of your UNIONED queries. i.e. select count(*) from ((select ...) a union (select ...) b union (select ...) c) a – Derek Aug 22 '11 at 14:14
Another ERROR: #1064 - You have an error in your SQL syntax; I will edit my post so you can see what I'm inputting. – ProgrammerGirl Aug 22 '11 at 14:18
Please see the EDIT in my post for the errors I'm getting from your suggestions. – ProgrammerGirl Aug 22 '11 at 14:26
You get the same error if you add "AS a" instead of "a" for the table aliases? – Derek Aug 22 '11 at 14:27
Yes. Any other ideas? This can't be THIS complicated, can it? Or is it just easier to use PHP to count the rows when a query is this complicated? – ProgrammerGirl Aug 22 '11 at 14:36

score 1 · Answer 2 · answered Aug 22 '11 at 14:08

1

Wrap the whole query as

SELECT COUNT(*) FROM (<Your query>)

answered Aug 22 '11 at 14:08

Stefan

5,644
4
24
31

That gives me the following error: #1248 - Every derived table must have its own alias – ProgrammerGirl Aug 22 '11 at 14:11

score 1 · Answer 3 · answered Aug 22 '11 at 16:02

Really use mysql_num_rows. For the union the database needs to build a temporary table anyway, which is the costly thing. Even if it were possible to do straight within SQL you won't save much by asking MySQL to give you that number.

The data isn't sent to PHP when you just use the mysql_num_rows function anyway.

score 1 · Answer 4 · edited May 23 '17 at 12:26

1

You need to specify a unique alias for your Column Col1 since it is used in a join which requires unique columns to match against each other. It will throw an error 1060 if it comes across duplicated columns. Check this: Duplicate column names in SQL query

In your case, the MySQL gets confused with Col1 in table 1 and table 2 and hence the error. Once you have used that, you can SELECT COUNT(*) from YourQuery

edited May 23 '17 at 12:26

Community

1
1

answered Aug 22 '11 at 16:10

reggie

13,313
13
41
57

That worked! Also, just removing ', Alias2 . * ' from all 3 SELECTS worked as well. Thanks reggie! – ProgrammerGirl Aug 22 '11 at 16:12

score 1 · Accepted Answer · edited May 23 '17 at 09:59

1

You need to specify a unique alias for your Column Col1 since it is used in a join which requires unique columns to match against each other. It will throw an error 1060 if it comes across duplicated columns. Check this: Duplicate column names in SQL query

In your case, the MySQL gets confused with Col1 in table 1 and table 2 and hence the error. Once you have used that, you can SELECT COUNT(*) from YourQuery

edited May 23 '17 at 09:59

Community

1
1

answered Aug 22 '11 at 16:12

reggie

13,313
13
41
57

1

That worked! Also, just removing ', Alias2 . * ' from all 3 SELECTS worked as well. Thanks reggie! – ProgrammerGirl Aug 22 '11 at 16:14

MySQL: How to COUNT the number of rows returned by a Complicated Query

5 Answers5