MySQL Error 1066 – How to Fix Not Unique Table/Alias Issue

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

You can use conditional sum for this calculation something as

select 
date_format(created,'%d-%m') as created_date , 
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;

Here is a test case

mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open     |
|    2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new      |
|    3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
|    4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open     |
|    5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new      |
|    6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> select 
    -> date_format(created,'%d-%m') as created_date , 
    -> sum( case when status='open' then 1 else 0 end )+
    -> sum( case when status='new' then 1 else 0 end )+
    -> sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
    -> from test
    -> where
    -> YEAR(Created) = '2015'
    -> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    2 |
+--------------+------+
2 rows in set (0.00 sec)

UPDATE The count of open is a incremental as per the comment. So the following should do the trick

select
created_date,
@open:= @open+open_ticket-resolved as open
from(
 select 
 date_format(created,'%d-%m') as created_date , 
 sum( case when status='open' then 1 else 0 end )+
 sum( case when status='new' then 1 else 0 end )+
 sum( case when status='resolved' and ( date(resolved) > date(created) or date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 sum(case when status = 'resolved' and date(resolved) = date(created) then 1 else 0 end) as resolved
 from test
 where
 YEAR(Created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ; 

From the above sample data it will have

 +--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
+--------------+------+

UPDATE:

Count the previous tickets which got resolved on a given date and deduct that from the current day open ticket, for that you need a left join something as

select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
 select 
 date_format(t.created,'%d-%m') as created_date , 
 sum( case when t.status='open' then 1 else 0 end )+
 sum( case when t.status='new' then 1 else 0 end )+
 sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 coalesce(tot,0) as resolved_ticket
 from test t left join(
    select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
 )x on date(x.resolved) = date(t.created) 
 where
 YEAR(t.created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ; 

OUTPUT :

+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
| 12-05        |    7 |
+--------------+------+