MYSQL计算行而不是显示结果

Question

So I have the following query, which I use it to get some analytics stats.

SELECT count(*) as total,CONCAT(YEAR(created),'-',MONTH(created),'-',DAY(created))  
as date_only  FROM logs where action = 'banner view'  
and created BETWEEN '2015-07-03 21:03'
AND '2017-08-02 21:03' group by date_only order by created asc

This works, and it gives me this:

So what I actually need is, the total count of the rows in this case is 20, this is a dummy example, but I need to use this count to check before showing the stats if the data is too big to be displayed on a graphic.

Can this be achieved?

//LE

So the process will be like this: 1. Get a count of the total rows, if the count of rows is smaller than X(number will be in config and it will be a basic if statement), then go ahread and run the above query.

More info: I actually use this query to display the stats, I just need to adapt it in order to show the total count rows

So the result of thquery should be

total | 20 in this case

Answer 1

I think you would want to use a derived table. Just wrap your original query in parenthesis after the FROM and then give the derived table an alias (in this case tmp). Like so:

SELECT count(*) FROM (
   SELECT count(*) as total,CONCAT(YEAR(created),'-',MONTH(created),'-',DAY(created))  

   as date_only  FROM logs where action = 'banner view'  
   and created BETWEEN '2015-07-03 21:03'
   AND '2017-08-02 21:03' group by date_only order by created asc
) as tmp;

If I understand what you want to do correctly, this should work. It should return the actual number of results from your original query.

What's happening is that the results of the parenthesized query are getting used as a sort of virtual table to query against. The parenthesized query returns 20 rows, so the "virtual" table has 20 rows. The outer count(*) just counts how many rows there are in that virtual table.