sql query syntax case, join and group by

user_9488865 0
Viewed 42

I have two mysql tables:

mysql> desc macToNames;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| mac   | varchar(17)   | YES  | UNI | NULL    |       |
| Name  | text          | YES  |     | NULL    |       |
| Seen  | decimal(10,0) | NO   |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+

and

mysql> desc stats;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| mac    | varchar(17)   | YES  |     | NULL    |       |
| ipAddr | text          | YES  |     | NULL    |       |
| epoch  | decimal(10,0) | NO   |     | NULL    |       |
| sent   | decimal(10,0) | NO   |     | NULL    |       |
| recv   | decimal(10,0) | NO   |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

I have a query as below, with my limited join skills:

select case when macToNames.Name is null then stats.mac else macToNames.Name end as 'Hostname'
     ,stats.mac as 'mac',stats.ipAddr as 'ipAddr'
     ,stats.epoch as 'epoch'
     ,stats.sent as 'sent'
     ,stats.recv as 'recv' 
  from stats 
  left
  join macToNames 
    on macToNames.mac = stats.mac
  limit 5;
+-------------------+-------------------+---------------+------------+----------+-----------+
| Hostname          | mac               | ipAddr        | epoch      | sent     | recv      |
+-------------------+-------------------+---------------+------------+----------+-----------+
| x1                | 39-F2-BC-2F-4D-E9 | 192.168.1.232 | 1593836118 |   307197 |    623309 |
| someho-lxc        | 29-F2-BC-2F-4D-E9 | 192.168.1.52  | 1593836118 |  4273599 |   4207535 |
| 39-F2-BC-2F-4D-E9 | 39-F2-BC-2F-4D-E9 | 192.168.1.216 | 1593836118 |     4899 |      6503 |
| tinker            | 39-F2-AC-2F-4D-E9 | 192.168.1.166 | 1593836119 |    60312 |   8563601 |
| u1                | 3A-F2-BC-2F-4D-E9 | 192.168.1.172 | 1593836119 |      380 |       380 |
+-------------------+-------------------+---------------+------------+----------+-----------+

Here's where it gets difficult for me - I wish to run the above query as a sub-query

mysql> select Hostname,mac from (select case when macToNames.Name is null then stats.mac else macToNames.Name end as 'Hostname',stats.mac as 'mac',stats.ipAddr as 'ipAddr',stats.epoch as 'epoch',stats.sent as 'sent',stats.recv as 'recv' from stats left join macToNames on macToNames.mac=stats.mac);
ERROR 1248 (42000): Every derived table must have its own alias

and, another:

mysql> select Hostname,mac,sum(stats.Sent)/(1000000000) as 'Sent',sum(stats.Recv)/1000000000 as 'Recv' group by mac from (select case when macToNames.Name is null then stats.mac else macToNames.Name end as 'Hostname',stats.mac as 'mac',stats.ipAddr as 'ipAddr',stats.epoch as 'epoch',stats.sent as 'sent',stats.recv as 'recv' from stats left join macToNames on macToNames.mac=stats.mac);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by mac from (select case when macToNames.Name is null then stats.mac else ' at line 1

I am trying to generate a report on total traffic by each mac, and also show the friendly name for that device. I am totally lost between the case and join - can you please point me the right direction?

mysql sql
1 Answers

As error indicates, you must provide an alias to derived tables or subqueries in FROM or JOIN clause. Additionally, for aggregate queries all SELECT columns must be present in GROUP BY (which follows FROM and JOIN clauses). Notice below all columns in top level is qualified by the alias sub. Also, your column aliases of stats fields are redundant since no expression is used and they repeat original column names. Finally, for many zeroes, use E for readability.

select sub.Hostname
       , sub.mac
       , sum(sub.Sent)/1E9 as 'Sent'
       , sum(sub.Recv)/1E9 as 'Recv' 
from 
  (select case 
              when macToNames.Name is null 
              then stats.mac 
              else macToNames.Name 
           end as 'Hostname'
           , stats.mac
           , stats.ipAddr
           , stats.epoch
           , stats.sent
           , stats.recv
    from stats 
    left join macToNames 
         on macToNames.mac=stats.mac
  ) as sub
group by sub.Hostname
       , sub.mac

In MySQL 8.0+, you can use CTEs in place of derived tables:

with sub AS (
   select case 
            when macToNames.Name is null 
            then stats.mac 
            else macToNames.Name 
          end as 'Hostname'
          , stats.mac
          , stats.ipAddr
          , stats.epoch
          , stats.sent
          , stats.recv
   from stats 
   left join macToNames 
         on macToNames.mac=stats.mac)

select sub.Hostname
       , sub.mac
       , sum(sub.Sent)/1E9 as 'Sent'
       , sum(sub.Recv)/1E9 as 'Recv' 
from sub
group by sub.Hostname
       , sub.mac
user_14224510
Related