CODEWITHSUNDEEP
mysqlbash
Evgheni Antropov
Evgheni Antropov

Reputation: 73

mysql count for several tables in one request

I have following output:

    root@localhost [~]# mysql -e "SELECT TABLE_ROWS from information_schema.Tables where TABLE_SCHEMA= 'testdb' && TABLE_NAME = 'requests';"
    +------------+
    | TABLE_ROWS |
    +------------+
    |    9566846 |
    +------------+

    root@localhost [~]# mysql -e "select count(*) from testdb.requests where created_at like '2012%';"
    +----------+
    | count(*) |
    +----------+
    |   301438 |
    +----------+
    root@localhost [~]# mysql -e "select count(*) from testdb.requests where created_at like '2013%';"
    +----------+
    | count(*) |
    +----------+
    |    24917 |
    +----------+

How will be better, using mysql request to do the same with one request to have new output like

    +------------------+-----------------------+
    | year             | count(*)              |
    +------------------+-----------------------+
    | 2009             | 1066268               |
    | 2010             | 6799553               |
    | 2011             | 1374685               |
    | 2012             | 301438                |
    | 2013             | 24917                 |
    | total            | 9566846               |
    +------------------+-----------------------+

Thank you in advance, Evgheni

Upvotes: 0

Views: 107

Answers (3)

peterm
peterm

Reputation: 92795

Try this one

SELECT YEAR(created_at) AS `year`,
       COUNT(*) AS `count` 
  FROM testdb.requests 
 GROUP BY YEAR(created_at)
 UNION ALL
SELECT 'total' AS `year`, 
       TABLE_ROWS AS `count`
  FROM information_schema.Tables 
 WHERE TABLE_SCHEMA= 'testdb' AND 
       TABLE_NAME = 'requests'

or

SELECT YEAR(created_at) AS `year`,
       COUNT(*) AS `count` 
  FROM testdb.requests 
 GROUP BY YEAR(created_at)
 UNION ALL
SELECT 'total' AS `year`, 
       COUNT(*) AS `year`
  FROM testdb.requests

It produces output like this:

+-------+-------+
| year  | count |
+-------+-------+
| 2012  |     6 |
| 2013  |     7 |
| total |    13 |
+-------+-------+

Here is sqlfiddle

Upvotes: 1

joese
joese

Reputation: 86

You could try to use a group statement

select 
    DATE_FORMAT(created_at, '%d') as year, 
    count(*) as count
from
    testdb.requests
group by year;

Upvotes: 1

Devart
Devart

Reputation: 121932

Try to use aggregate functions -

SELECT
  YEAR(created_at) yesr,
  COUNT(*) cnt
FROM
  testdb.requests
GROUP BY year;

To count total use WITH ROLLUP modifier -

SELECT
  YEAR(created_at) yesr,
  COUNT(*) cnt
FROM
  testdb.requests
GROUP BY year WITH ROLLUP;

Upvotes: 1

Related Questions