SQL query summary issue

I'm new to SQL and trying to create a total summary of a working SQL query. It's listing the total results from one month of data. Now I need the total values of the outcome of the query. So I created a 'query in a query' piece of SQL, but it ain't working because my lack of SQL knowledge. I guess it's an easy fix for you pro's :-)

The working SQL query with the daily outcome of one month:

SELECT
          DATE_FORMAT(date, '%d/%m/%y') AS Datum,
          COUNT(*) AS Berichten,
          SUM(CASE WHEN virusinfected>0 THEN 1 ELSE 0 END) AS Virus,
          SUM(CASE WHEN (virusinfected=0 OR virusinfected IS NULL) AND isspam>0 THEN 1 ELSE 0 END) AS Ongewenst,
          SUM(CASE WHEN (virusinfected=0 OR virusinfected IS NULL) AND (isspam=1) AND isrblspam>0 THEN 1 ELSE 0 END) AS RBL,
          SUM(size) AS Grootte
         FROM
          maillog
         WHERE
          1=1
        AND (1=1)
        AND
         date < '2017-04-01'
        AND
         date >= '2017-03-01'
        AND
         to_domain = 'domain1.nl'
        OR
        date < '2017-04-01'
        AND
         date >= '2017-03-01'
        AND
         to_domain = 'domain2.nl'

         GROUP BY
          Datum
         ORDER BY
          date

The incorrect query trying to create the monthly totals:

SELECT  Datum,
    SUM(Berichten) AS Berichten,
    SUM(Virus) AS Virus,
    SUM(Ongewenst) AS Ongewenst,
    SUM(RBL) AS RBL,
    SUM(Grootte) AS Grootte,
    FROM    (   SELECT
                  DATE_FORMAT(date, '%d/%m/%y') AS Datum,
                  COUNT(*) AS Berichten,
                  SUM(CASE WHEN virusinfected>0 THEN 1 ELSE 0 END) AS Virus,
                  SUM(CASE WHEN (virusinfected=0 OR virusinfected IS NULL) AND isspam>0 THEN 1 ELSE 0 END) AS Ongewenst,
                  SUM(CASE WHEN (virusinfected=0 OR virusinfected IS NULL) AND (isspam=1) AND isrblspam>0 THEN 1 ELSE 0 END) AS RBL,
                  SUM(size) AS Grootte
                 FROM
                  maillog
                 WHERE
                  1=1
                AND (1=1)
                AND
                 date < '2017-04-01'
                AND
                 date >= '2017-03-01'
                AND
                 to_domain = 'domain1.nl'
                OR
                date < '2017-04-01'
                AND
                 date >= '2017-03-01'
                AND
                 to_domain = 'domain2.nl'

                 GROUP BY
                  Datum
                 ORDER BY
                  date
            ) t
    GROUP BY Datum;

Thanks in advance.

Solution

What you want can be done with just a little addition to your first SQL statement: add with rollup after the group by clause:

GROUP BY Datum WITH ROLLUP

It will run more efficiently than the version with sub-query, although it could work that way, but you should then remove the outer group by clause and not select Datum there, since you don't want the totals per date any more, but overall.

Still, you will lose the details and only get the overall totals then. You would have to use a union with your original query to get both levels of totals. You can imagine that the with rollup modifier will do the job more efficiently.