SELECT 
        count(distinct(userId)) as numberUsers,
        sum(productive) as productive,
        sum(unproductive) as unproductive,
        sum(uncategorized) as uncategorized,
        count(distinct(surf)) as surf
      FROM (
            SELECT
              userId,
              groupId,
              groupName, 
              sum(case prod when 1 then total_sec else 0 end) as productive,
              sum(case prod when 0 then total_sec else 0 end) as unproductive,
              sum(case when prod IS NULL then total_sec end) as uncategorized,
              sum(total_sec) as total,
              surfdate as surf
            FROM 
              (SELECT 
                report_browsingtime_v2.userId as userId,
                -- `user`.userName users, 
                usergroup.id as groupId,
                usergroup.groupName,
                IF(url_productivity_exception.id IS NULL, url_productivity.isProductive, url_productivity_exception.isProductive) prod, 
                SUM(duration) AS total_sec,
                date(surfDateTime) surfdate, 
                time(surfDateTime) surftime
              FROM 
                report_browsingtime_v2
                INNER JOIN `user` ON(user.id = report_browsingtime_v2.userId)
                INNER JOIN usergroup ON(usergroup.id = user.userGroupId)
                INNER JOIN location ON(location.location = REPLACE(usergroup.pattern,':*',''))
                LEFT OUTER JOIN url_productivity ON(url_productivity.urlShortId = report_browsingtime_v2.urlShortId AND url_productivity.locationId = location.id AND url_productivity.groupId = 0)
                LEFT OUTER JOIN url_productivity as url_productivity_exception ON(url_productivity_exception.urlShortId = report_browsingtime_v2.urlShortId AND url_productivity_exception.locationId = location.id AND url_productivity_exception.groupId = usergroup.id)
                INNER JOIN urlshort ON(urlshort.id = report_browsingtime_v2.urlShortId)
                INNER JOIN clientpc cp ON cp.id = report_browsingtime_v2.pcId
                LEFT JOIN location l ON (l.id = url_productivity.locationId)
              WHERE 
                (usergroup.id IN (2,3,9,10,18,23,24,25,28,29,36,37,38,39,41,43,44,55,56,76,77,83,87,89,97,103,109,110) ) 
                AND (date(surfDateTime) BETWEEN date('2021-05-29') and date('2021-06-29')) 
                AND hour(surfDateTime) in(8,9,10,11,12,14,15,16,17)
                AND date(surfDateTime) not in ('2021-06-10','2021-06-26')
                AND (  0=0 AND dayOfWeek(surfDateTime) in (2,3,4,5,6) AND (l.location = cp.location OR l.location IS NULL)  ) 
              GROUP BY 
                report_browsingtime_v2.userId, 
                url_productivity.isProductive,
                report_browsingtime_v2.userId
                
              
              
            ) AS timeTable
            GROUP BY groupId, userId 

            UNION

            SELECT	
              rpuh.userId as userId,
              -- u.username as userName,
              g.id as groupId,
              g.groupName,
              SUM(IF((IFNULL(`spv`.`productivityId`,`sp`.`productivityDefId`) = 1),`rpuh`.`softwareActiveTime`,0)) AS productive,
              SUM(IF((IFNULL(`spv`.`productivityId`,`sp`.`productivityDefId`) = 2),`rpuh`.`softwareActiveTime`,0)) AS unproductive,
              SUM(IF((IFNULL(`spv`.`productivityId`,`sp`.`productivityDefId`) in (3, 4, 5))  ,`rpuh`.`softwareActiveTime`,0)) AS uncategorized,
              SUM(rpuh.softwareActiveTime) as total,
              date(rpuh.snapshotDateTime) as surf
            FROM
              report_productivity_user_hour rpuh
              INNER JOIN USER u ON(u.id = rpuh.userId)
              INNER join usergroup g on(g.id = u.userGroupId)
              LEFT JOIN clientpc c ON(c.id = rpuh.pcId)
              INNER JOIN software_productivity_view spv ON(spv.id = rpuh.softwareId)
              INNER JOIN softwarelicense s ON(s.id = rpuh.softwareId AND s.productName NOT IN('PC_ACTIVE','PC_IDLE'))
              LEFT JOIN softwareproductivity sp ON(sp.softwareId = rpuh.softwareId)
            WHERE  
              DATE_FORMAT(snapshotDateTime,'%Y-%m-%d') BETWEEN '2021-05-29' AND '2021-06-29' 
              AND s.softwareName not in ('IDLE','LOCKAPP.EXE','CHROME.EXE','IEXPLORE.EXE','MICROSOFTEDGE.EXE','MICROSOFTEDGECP.EXE','FIREFOX.EXE','SAFARI.EXE','MSEDGE.EXE')
              AND hour(rpuh.snapshotDateTime) in(8,9,10,11,12,14,15,16,17)
            AND dayOfWeek(rpuh.snapshotDateTime) in (2,3,4,5,6)
            AND date(rpuh.snapshotDateTime) not in ('2021-06-10','2021-06-26') 
              AND u.usergroupId IN (2,3,9,10,18,23,24,25,28,29,36,37,38,39,41,43,44,55,56,76,77,83,87,89,97,103,109,110) 
            GROUP BY 
              g.id, rpuh.userId	
            )a