Cannot nest aggregate operations

Question

I am trying to run the following query :

Select T, COUNT(T) as CountOf, (COUNT(T)*100)/(SUM(COUNT(T))) AS percen FROM
(
select prod as T, x,y,z from table1
UNION ALL select prod as T, x,y,z from table2
UNION ALL select prod as T, x,y,z from table3
)
AS m
GROUP BY T ORDER BY COUNT(T) DESC;

The above query gives the error. I am able to run the query successfully without the 3rd parameter i.e. (COUNT(T)*100)/(SUM(COUNT(T))) AS percent (Calculates the percentage distribution of the 2nd parameter i.e. CountOf)

But when i include the percentage parameter, it gives error. Any help on this would be appreciated.

Answer 1

I don't like using distinct but if you are using sql server you can try

Select DISTINCT T, COUNT(*) OVER (PARTITION BY T) as CountOf,  ((COUNT(*) OVER (PARTITION BY T) * 100) / COUNT(*) OVER ()) AS percen FROM
(
select prod as T, x,y,z from table1
UNION ALL select prod as T, x,y,z from table2
UNION ALL select prod as T, x,y,z from table3
)
AS m

CTE option

WITH cteUnion AS (
    SELECT prod AS T, x,y,z FROM table1
    UNION ALL 
    SELECT prod AS T, x,y,z FROM table2
    UNION ALL 
    SELECT prod AS T, x,y,z FROM table3
),
cteUnionWithTotalCount AS (
    SELECT *, COUNT(*) OVER () AS TotalCount FROM cteUnion
)
SELECT  T, COUNT(*) AS CountOf, ((COUNT(*) * 100)/ TotalCount) AS percen
FROM    cteUnionWithTotalCount
GROUP BY T, TotalCount
ORDER BY COUNT(*)

Answer 2

You need to change the SUM to a SUM OVER, but this is not supported by MySQL:

SELECT T, COUNT(T) AS CountOf, 
   (COUNT(T)*100)/(SUM(COUNT(T)) OVER ()) AS percen
FROM
 (
   SELECT prod AS T, x,y,z FROM table1
   UNION ALL 
   SELECT prod AS T, x,y,z FROM table2
   UNION ALL 
   SELECT prod AS T, x,y,z FROM table3
  ) AS m
GROUP BY T 
ORDER BY COUNT(T) DESC;