I have a sample table name (info) in mysql database as follows:
id dateAdded
----------------------------
1 2013-12-24 03:03:19
2 2013-12-24 03:04:19
3 2013-12-24 03:06:14
4 2013-12-24 03:07:23
5 2013-12-25 03:04:19
6 2013-12-26 03:02:19
7 2013-12-26 03:03:19
I have another table name (error) as follows:
id date
----------------------------
11 2013-12-24 03:03:19
22 2013-12-24 03:04:19
33 2013-12-25 03:06:14
53 2013-12-25 03:04:19
62 2013-12-26 03:02:19
I want to COUNT how many ids from the two tables (info and error) with the same dates , so the result out will be :
date countinfo counterror
----------------------------------------
2013-12-24 4 2
2013-12-25 1 2
2013-12-26 2 1
Please if you could help me out with the Mysql query
Give this a try, maybe it can be written in a better way but this gives the desired output.
SELECT
DATE(Sub.dateadded) as `Date`,
( SELECT count(id)
FROM test.info
WHERE DATE(info.dateadded)=DATE(Sub.dateAdded)) as `CountInfo`,
( SELECT count(id)
FROM test.`error`
WHERE DATE(`error`.`date`)=DATE(Sub.dateAdded)) as `ErrorInfo`
FROM
(
SELECT `date` as dateadded
from test.`error`
UNION SELECT dateadded
FROM test.info
) Sub
GROUP BY Date(Sub.dateadded)
Notice that my database name used here is test, change that to your database name.
Since the date field is from different tables, you must UNION them in a subquery so that you can get the relevant dates. Then from there a simple subquery in the select is executed with the dateparameter.
In the future, try to name your tables with names that is not a datatype or function name etc, then the ` is not needed to wrap the database,table,column names
EDIT
If you want specific dates, just make use of WHERE.
Add this line before the GROUP BY
WHERE DATE(Sub.dateadded) IN ('2013-12-24','2013-12-25')
If you want between a time span you can do this
WHERE DATE(Sub.dateadded) BETWEEN '2013-12-24' AND '2013-12-30'
This will give the dates available between 24-30 of December.
Try this for a pure MySQL approach:
SELECT dateAdded, COUNT(*) as count_info FROM info GROUP BY dateAdded;
SELECT date, COUNT(*) as count_error FROM error GROUP BY date;
Try this, please rename the table name that I have provided.
Info : Join both table get the count of distinct Id
select Date(dateadded) as datepart,
count(distinct(infotable.id)) as countInfo,
count(distinct(errortable.id)) as counterror from infotable inner join errortable
on Date(infotable.dateadded)=Date(errortable.date)
group by datepart