MYSQL count rows instead of showing results

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

This is a little vague. What is the criteria you are using to know if it's too big to be displayed in a graphic? — JakeParis
– JakeParis, Commented Aug 2, 2017 at 18:19
Well in case of "minutes" for example I can have 100k+ of dots on a graphic, I will now if it's too big, by first getting the count result and if it's smaller than X, than it will go ahead and actually run this query in order to display the results — Uffo
– Uffo, Commented Aug 2, 2017 at 18:22
I just need to adapt this query in order to give me the total count of the rows.. if posible — Uffo
– Uffo, Commented Aug 2, 2017 at 18:23
So the way you're currently doing it is to use a count query to check the size, then if it's small enough, query again for the actual results? — JakeParis
– JakeParis, Commented Aug 2, 2017 at 18:25

JakeParis · Accepted Answer · 2017-08-02 18:42:45Z

2

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.

edited Aug 2, 2017 at 18:42

answered Aug 2, 2017 at 18:35

JakeParis

11.2k6 gold badges44 silver badges67 bronze badges

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

3 Comments

Uffo Over a year ago

Can you post some info on what could I read in order to understand your solution, so basically count(*) will count the rows from (select...) ?

JakeParis Over a year ago

@Uffo Sure, I updated my answer with some more explanation.

vjy tiwari Over a year ago

@jakeparis Don't use order by clause , because don't need in the particular Question

manassehkatz-Moving 2 Codidact · Accepted Answer · 2017-08-02 18:39:11Z

1

Based on the PHP tag, I assume you are using PHP to send the queries to MySQL. If so, you can use mysqli_num_rows to get the answer.

If your query result is in $result then:

$total = mysqli_num_rows($result);

Slightly different syntax for Object Oriented style instead of procedural style.

The best part is you don't need an extra query. You perform the original query and get mysqli_num_rows as an extra without running another query. So you can figure out pagination or font size or whatever and then display without doing the query again.

edited Aug 2, 2017 at 18:39

answered Aug 2, 2017 at 18:33

manassehkatz-Moving 2 Codidact

3,0542 gold badges17 silver badges27 bronze badges

6 Comments

Uffo Over a year ago

I don't want to count the results from the array, I could do that already

manassehkatz-Moving 2 Codidact Over a year ago

This is NOT counting from the array. You can (I often do exactly this) do the main query and check the number of rows BEFORE doing anything with the data. Sometimes I will have different code based on 0 vs. 1 vs. >1 result rows, sometimes pagination, etc.

ficuscr Over a year ago

This is a better answer than the other one here. You should at least use SELECT FOUND_ROWS() if using the count in conjunction with the results of the first query. Otherwise the results could change between time you retrieve results and count. Bad design.

manassehkatz-Moving 2 Codidact Over a year ago

@ficuscr SELECT FOUND_ROWS() would work in a SQL command line. I am assuming based on the PHP tag that the actual code is PHP and guessing mysqli (there are a few other possibilities for MySQL in PHP, but all have a similar function available).

ficuscr Over a year ago

@manassehkatz saying I like your answer best. Not sure why would not use the PHP function. Just saying that doing a second count statement is in my mind not a good solution. Hope the data in not in flux.

|

Mary · Accepted Answer · 2017-08-02 18:40:33Z

0

This is an small query but works fine, and give me the total number of rows, you just need add your conditions.

SELECT COUNT(*) FROM table WHERE field LIKE '%condition%'

The group by I think you need to eliminated, becouse, this instead of count the records, divide in all your group by, example: records = 4, with group by you have
1 1 1 1

I hope this help you

answered Aug 2, 2017 at 18:40

Mary

1971 silver badge16 bronze badges

1 Comment

manassehkatz-Moving 2 Codidact Over a year ago

Nice try, but doesn't actually answer the question. In this case, the issue is trying to get the number of result rows, NOT the number of records included, so the GROUP BY can't be dropped without changing the answer.

vjy tiwari · Accepted Answer · 2017-08-02 18:42:00Z

0

You can try this way .

    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 HAVING total >=20 ) temp

answered Aug 2, 2017 at 18:42

vjy tiwari

8611 gold badge5 silver badges19 bronze badges

2 Comments

vjy tiwari Over a year ago

i was on mobile , so getting bit late :)

vjy tiwari Over a year ago

@jakeparis Don't use order by clause , because don't need in the particular Question

Collectives™ on Stack Overflow

MYSQL count rows instead of showing results

4 Answers 4

3 Comments

6 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

6 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related