By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

How can I retrieve records and a total count of the records - MySQL

P: 37
If I write:

SELECT * FROM table_name WHERE date BETWEEN "2014/07/01" AND "2014/07/04"

I get 4 records returned which is what I want. But I also want the total number of records which in this case is 4, but if I write:

SELECT *, count(*) AS total FROM table_name WHERE date BETWEEN "2014/07/01" AND "2014/07/04"

It displays total 4, but only one record.

How can I display all 4 records and "total 4"?
Jul 4 '14 #1

✓ answered by Luuk

The short answer is:
Expand|Select|Wrap|Line Numbers
  1. SELECT t.*, (SELECT count(*) 
  2.              FROM table_name
  3.              WHERE date BETWEEN "2014/07/01" AND "2014/07/04") as Count
  4. FROM table_name t
  5. WHERE t.date BETWEEN "2014/07/01" AND "2014/07/04" 
  6.  

Share this Question
Share on Google+
1 Reply


Expert 100+
P: 1,035
The short answer is:
Expand|Select|Wrap|Line Numbers
  1. SELECT t.*, (SELECT count(*) 
  2.              FROM table_name
  3.              WHERE date BETWEEN "2014/07/01" AND "2014/07/04") as Count
  4. FROM table_name t
  5. WHERE t.date BETWEEN "2014/07/01" AND "2014/07/04" 
  6.  
Jul 4 '14 #2

Post your reply

Sign in to post your reply or Sign up for a free account.