SQL: How to sum and divide values in one column based on values in another column?-CodePudding

I want to sum the values in the 'Count' column where the 'ID' = 1 and divide them by the total sum of the 'Count' column, grouping by 'Name'.

This is what my data looks like:

Name         ID         Count
David        1          2.0
Crystal      1          2.0
John         1          2.0   
David        2          5.0
Crystal      2          4.0
John         2          3.0
David        2          4.0
Crystal      2          3.0
John         2          2.0

This is what I want my final table to look like:

Name          Perc_Total
David         22.2%
Crystal       28.6%
John          40.0%

I would like the totals calculations in the final table to be a percentage.

I tried to use CASE expressions, but I kept getting errors saying: SQL Server Database Error: Divide by zero error encountered

This is the code:

select
  Name,
  (sum(case when ID = 1 then Count else 0 end) /
        nullif(sum(case when ID != 1 then count end),0)
       ) as 'Perc_Total'
from
data;

CodePudding user response：

SELECT name, ROUND((SUM(count) / CAST(Total.TotalAll AS FLOAT))  * 100, 2) As Perc_Total
FROM data CROSS JOIN 
     (SELECT SUM(count) As TotalAll 
      FROM data) Total
GROUP BY name, Total.TotalAll

CodePudding user response：

You want a group aggregate:

select
  Name,
  100 * sum(Count) /
        -- grand total
        sum(sum(Count)) over () as 'Perc_Total'
from data
group by name;

Btw, why do you tag Teradata if your error message talks about SQL Server?