How to get total count from query?

this is my query

SELECT  a.id,
        a.venue_id, 
        a.user_id, 
        m1.profilenam AS user_profilename,
        m1.photo_thumb AS user_photo_thumb,
        m2.profilenam AS venue_profilename, 
        m2.photo_thumb AS venue_photo_thumb 
FROM announce_arrival AS a 
    INNER JOIN members AS m1 
        ON a.user_id = m1.mem_id
    INNER JOIN members AS m2 
        ON a.venue_id = m2.mem_id
GROUP BY a.venue_id, a.user_id
LIMIT 0,10 
ORDER BY date DESC,
         time DESC

How can i use count(*) on this query,i use like this

SELECT DISTINCT COUNT(*) 
 FROM announce_arrival AS a       
 INNER JOIN members as m1 ON (a.user_id = m1.mem_id)
 INNER JOIN members as m2 ON (a.venue_id= m2.mem_id)
 GROUP BY a.venue_id, a.user_id 开发者_如何学PythonLIMIT 0,10  ORDER BY date DESC,time DESC;

but its showing COUNT(*) 7 3 1

i want total count .

You can wrap your query into select count(*), like this:

SELECT COUNT(*) FROM
  (SELECT a.id,a.venue_id, a.user_id, m1.profilenam as
    user_profilename,m1.photo_thumb AS user_photo_thumb,m2.profilenam AS
    venue_profilename, m2.photo_thumb AS venue_photo_thumb FROM announce_arrival
    AS a INNER JOIN members as m1 ON (a.user_id = m1.mem_id) INNER JOIN members
    as m2 ON (a.venue_id= m2.mem_id) GROUP BY a.venue_id, a.user_id)

You can wrap your query in another SELECT:

SELECT COUNT(*) AS total FROM (
SELECT DISTINCT COUNT(*) 
FROM announce_arrival AS a       
INNER JOIN members as m1 ON (a.user_id = m1.mem_id)
INNER JOIN members as m2 ON (a.venue_id= m2.mem_id)
GROUP BY a.venue_id, a.user_id LIMIT 0,10  ORDER BY date DESC,time DESC) AS t

or if you want sum of all DISTINCT COUNT(*) try:

SELECT SUM(cnt) AS total FROM (
SELECT DISTINCT COUNT(*) AS cnt
FROM announce_arrival AS a       
INNER JOIN members as m1 ON (a.user_id = m1.mem_id)
INNER JOIN members as m2 ON (a.venue_id= m2.mem_id)
GROUP BY a.venue_id, a.user_id LIMIT 0,10  ORDER BY date DESC,time DESC) AS t

From docs about found_rows():

To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;

mysql> SELECT FOUND_ROWS();

The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.

I.e., add SQL_CALC_FOUND_ROWS after SELECT in your first query and replace second query with SELECT FOUND_ROWS().

Get rid of GROUP BY, LIMIT and ORDER. They are useless and don't make sense (especially LIMIT) if you need a total count. DISTINCT doesn't make sense either.

SELECT COUNT(*) 
 FROM announce_arrival AS a       
 INNER JOIN members as m1 ON (a.user_id = m1.mem_id)
 INNER JOIN members as m2 ON (a.venue_id= m2.mem_id)

Try this

      SELECT COUNT(a.user_id) 
      FROM announce_arrival AS a       
      INNER JOIN members as m1 ON (a.user_id = m1.mem_id)
      INNER JOIN members as m2 ON (a.venue_id= m2.mem_id)
      GROUP BY  a.user_id LIMIT 0,10;

If you are using count no need to give order by