CODEWITHSUNDEEP
mysqlsql
Vijay
Vijay

Reputation: 111

MySQL SQL Union gives strange results

I have table in MySQL where I would like to get stats on finding usage limits of users for 3 cases

  1. Distinct IP addresses used to login in the past hour (column ip)
  2. Number of posts by the user in the past day (column cnt)
  3. Number of sessions for the user in the past 10 minutes (column authid is the username)

For the reason of feeding a control object I need these are three rows, so I started using MySQL UNION but I get strange results. Here is what I have

mysql> desc senderlimits;
+----------+--------------+------+-----+-------------------+-------+
| Field    | Type         | Null | Key | Default           | Extra |
+----------+--------------+------+-----+-------------------+-------+
| authid   | varchar(128) | NO   |     | NULL              |       |
| ip       | varchar(40)  | NO   |     | NULL              |       |
| cnt      | int(11)      | NO   |     | 1                 |       |
| datetime | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
+----------+--------------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)

mysql> select count(distinct ip) as ipl from senderlimits where authid='[email protected]' and datetime > date_sub(now(),INTERVAL 1 hour) UNION select sum(cnt) as cntl from senderlimits where authid='[email protected]' and datetime > date_sub(now(),INTERVAL 24 hour) UNION select sum(cnt) as vcnt from senderlimits where authid='[email protected]' and datetime > date_sub(now(),INTERVAL 10 minute) ;
+------+
| ipl  |
+------+
|    1 |
|   95 |
+------+
2 rows in set (0.04 sec)

mysql> select count(distinct ip) as ipl from senderlimits where authid='[email protected]' and datetime > date_sub(now(),INTERVAL 1 hour);
+-----+
| ipl |
+-----+
|   0 |
+-----+
1 row in set (0.01 sec)

mysql> select sum(cnt) as cntl from senderlimits where authid='[email protected]' and datetime > date_sub(now(),INTERVAL 24 hour) ;
+------+
| cntl |
+------+
|   95 |
+------+
1 row in set (0.02 sec)

mysql> select count(authid) as vcnt from senderlimits where authid='[email protected]' and datetime > date_sub(now(),INTERVAL 10 minute);
+------+
| vcnt |
+------+
|    1 |
+------+
1 row in set (0.02 sec)

mysql>

The results are very strange when I use the UNION option. Does any expert know why? or how I can achieve the same result as 0 95 1

in that order?

Upvotes: 0

Views: 214

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

UNION removes duplicates. You want UNION ALL:

select count(distinct ip) as ipl
from senderlimits
where authid = '[email protected]' and datetime > date_sub(now(),INTERVAL 1 hour)
UNION ALL
select sum(cnt) as cntl
from senderlimits
where authid = '[email protected]' and datetime > date_sub(now(),INTERVAL 24 hour)
UNION ALL
select sum(cnt) as vcnt
from senderlimits
where authid = '[email protected]' and datetime > date_sub(now(),INTERVAL 10 minute) ;

However, you shouldn't depend on the ordering of the results being the same as the order of the subqueries (this is not guaranteed, even if it often happens in practice). You should include a column specifying which number means what:

select '1 hour' as which, count(distinct ip) as ipl
from senderlimits
where authid = '[email protected]' and datetime > date_sub(now(),INTERVAL 1 hour)
UNION ALL
select '24 hour' as which, sum(cnt) as cntl
from senderlimits
where authid = '[email protected]' and datetime > date_sub(now(),INTERVAL 24 hour)
UNION ALL
select '10 minutes' as which, sum(cnt) as vcnt
from senderlimits
where authid = '[email protected]' and datetime > date_sub(now(),INTERVAL 10 minute) ;

Upvotes: 1

Related Questions