Gajendra Singh Rajput
Gajendra Singh Rajput

Reputation: 100

data count from two different table by group by

i have two table a,b and both tables structure is same as below

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| srno      | int(11)      | NO   | PRI | NULL    | auto_increment |
| domain    | varchar(50)  | NO   | UNI | NULL    |                |
| timestamp | timestamp    | YES  |     | NULL    |                |
| source    | varchar(100) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

i want domain count from both table on particular date so plz help

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

I think the tricky part of the question is that you need union all to bring the tables together. The rest of the question is unclear. If I interpret it as getting all domains with their counts on one particular day:

select domain, count(*)
from ((select a.* from a) union all
      (select b.* from b)
     ) ab
where timestamp >= $YOURDATE and
      timestamp < date_add($YOURDATE, interval 1 day)
group by domain;

Note: Having two tables with exactly the same structure is usually a sign of poor database design. A single entity is usually placed in a single table. More typically, there would be one table with a column specifying "a" or "b".

Upvotes: 2

Related Questions