How to properly aggregate? I have a table for logs with over 6 million records. I want a query that shows me all the occurrences where the src goes to the same destination/port.

I tried this:

SELECT src, dst, dstport, COUNT(src) AS Hits 
FROM logs  
GROUP BY src 
ORDER BY Hits DESC;


Not sure that this query is giving me exactly what I want. Additionally, what is the best way to exclude specific port? Say dstport = 53?

I would like the to show each time each src reached the same dst &amp; dstport while keeping count of the occurances. Say src 10.110.0.10 reached dst 10.2.9.124:53 2,345,568 times &amp; also reached 192.168.9.18:80 174 times ; then the result would look like:
 Example:


+-----------------+-----------------+---------+---------+
| src             | dst             | dstport | Hist    |
+-----------------+-----------------+---------+---------+
| 10.110.0.10     | 10.2.9.124      |      53 | 2345568 |
+-----------------+-----------------+---------+---------+
| 10.110.0.10     | 192.168.9.18    |      80 |     174 |
+-----------------+-----------------+---------+---------+

However with the query above I am getting this results:


+-----------------+-----------------+---------+---------+
| src             | dst             | dstport | Hist    |
+-----------------+-----------------+---------+---------+
| 10.110.0.10     | 10.2.9.124      |      53 | 1443780 |
| 10.110.0.10     | 192.168.9.124   |      53 | 1402210 |
| 10.192.31.23    | 10.192.1.120    |    8082 |  319507 |
| 10.192.31.19    | 10.192.1.186    |    8081 |  319203 |
| 192.168.31.131  | 192.168.31.130  |      80 |  290818 |
+-----------------+-----------------+---------+---------+
To see everything with aggregation, you could do this

SELECT src,CONCAT(dst,':',dstport) dsp_port,COUNT(1) hits
FROM logs
GROUP BY src,dsp_port;


To see subtotals

SELECT
    IF
    (
        ISNULL(src)=1,
        'Total',
        IF
        (
            ISNULL(dst_port)=1,
            CONCAT(src,' Total'),
            CONCAT(src,' ',dst_port)
        )
    ) Statistic,hits
FROM
(
    SELECT src,CONCAT(dst,':',dstport) dst_port,COUNT(1) hits
    FROM logs
    GROUP BY src,dst_port
    WITH ROLLUP
) A;


Using your sample data

mysql&gt; DROP DATABASE IF EXISTS rob_d;
Query OK, 1 row affected (0.02 sec)

mysql&gt; CREATE DATABASE rob_d;
Query OK, 1 row affected (0.00 sec)

mysql&gt; USE rob_d
Database changed
mysql&gt; CREATE TABLE logs
    -&gt; (
    -&gt;     src CHAR(15) NOT NULL,
    -&gt;     dst CHAR(15) NOT NULL,
    -&gt;     dstport  INT NOT NULL
    -&gt; );
Query OK, 0 rows affected (0.03 sec)

mysql&gt; INSERT INTO logs (src,dst,dstport) VALUES
    -&gt; ('10.110.0.10',   '10.2.9.124'    ,53),
    -&gt; ('10.110.0.10',   '192.168.9.124' ,53),
    -&gt; ('10.192.31.23',  '10.192.1.120'  ,8082),
    -&gt; ('10.192.31.19',  '10.192.1.186'  ,8081),
    -&gt; ('192.168.31.131','192.168.31.130',80);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql&gt; SELECT * FROM logs;
+----------------+----------------+---------+
| src            | dst            | dstport |
+----------------+----------------+---------+
| 10.110.0.10    | 10.2.9.124     |      53 |
| 10.110.0.10    | 192.168.9.124  |      53 |
| 10.192.31.23   | 10.192.1.120   |    8082 |
| 10.192.31.19   | 10.192.1.186   |    8081 |
| 192.168.31.131 | 192.168.31.130 |      80 |
+----------------+----------------+---------+
5 rows in set (0.00 sec)


Here is the query's output

mysql&gt; SELECT
    -&gt;     IF
    -&gt;     (
    -&gt;         ISNULL(src)=1,
    -&gt;         'Total',
    -&gt;         IF
    -&gt;         (
    -&gt;             ISNULL(dst_port)=1,
    -&gt;             CONCAT(src,' Total'),
    -&gt;             CONCAT(src,' ',dst_port)
    -&gt;         )
    -&gt;     ) Statistic,hits
    -&gt; FROM
    -&gt; (
    -&gt;     SELECT src,CONCAT(dst,':',dstport) dst_port,COUNT(1) hits
    -&gt;     FROM logs
    -&gt;     GROUP BY src,dst_port
    -&gt;     WITH ROLLUP
    -&gt; ) A;
+----------------------------------+------+
| Statistic                        | hits |
+----------------------------------+------+
| 10.110.0.10 10.2.9.124:53        |    1 |
| 10.110.0.10 192.168.9.124:53     |    1 |
| 10.110.0.10 Total                |    2 |
| 10.192.31.19 10.192.1.186:8081   |    1 |
| 10.192.31.19 Total               |    1 |
| 10.192.31.23 10.192.1.120:8082   |    1 |
| 10.192.31.23 Total               |    1 |
| 192.168.31.131 192.168.31.130:80 |    1 |
| 192.168.31.131 Total             |    1 |
| Total                            |    5 |
+----------------------------------+------+
10 rows in set (0.00 sec)

mysql&gt;