mysql - total sum of all grouped count(*) 's with order by and limit (fiddle included)

Question

Having trouble figuring this one out... I know what is wrong with the below, but not sure how to go about getting the results I want. Having two separate queries is not possible without a lot of extra refactoring which I want to avoid by doing this is one query... even though the TotalSum is duplicated for each result in the set.

SELECT
    data_logs.event_target AS Name,
    COUNT(*) AS Total,
    SUM(COUNT(*)) OVER() AS TotalSum                    
FROM data_logs

GROUP BY Name
ORDER BY Total DESC
LIMIT 10

What I want is TotalSum to be the total of all the grouped COUNT(*)'s (10 in this case). The problem is "windowing execution occurs before ORDER BY, LIMIT, and SELECT DISTINCT" so the total ends up being wrong... it is the total of ALL rows instead of the 'top 10' with my limit.

Array
(
    [0] => Array
        (
            [Name] => somename0
            [Total] => 11
            [TotalSum] => 61
        )

    [1] => Array
        (
            [Name] => somename1 
            [Total] => 4
            [TotalSum] => 61
        )

    [2] => Array
        (
            [Name] => somename2 
            [Total] => 3
            [TotalSum] => 61
        )

    [3] => Array
        (
            [Name] => somename3 
            [Total] => 2
            [TotalSum] => 61
        )

    [4] => Array
        (
            [Name] => somename4
            [Total] => 2
            [TotalSum] => 61
        )

    [5] => Array
        (
            [Name] => somename5
            [Total] => 2
            [TotalSum] => 61
        )

    [6] => Array
        (
            [Name] => somename6 
            [Total] => 2
            [TotalSum] => 61
        )

    [7] => Array
        (
            [Name] => somename7
            [Total] => 1
            [TotalSum] => 61
        )

    [8] => Array
        (
            [Name] => somename8
            [Total] => 1
            [TotalSum] => 61
        )

    [9] => Array
        (
            [Name] => somename9
            [Total] => 1
            [TotalSum] => 61
        )

)

In the above [TotalSum] should be 29 for all of them... the total of all 10 [Total] values.

FIDDLE : https://www.db-fiddle.com/f/agn932Q9eXxhcm3CE26THe/2

Answer 1

You can aggregate and limit first in a subquery, then compute the grand total:

SELECT t.*, SUM(total) OVER() AS TotalSum
FROM (
    SELECT event_target AS Name, COUNT(*) AS Total
    FROM data_logs
    GROUP BY Name
    ORDER BY Total DESC
    LIMIT 10
) t