# Calculating total against subtotal

Hi,

I have a data set as follows:

 ID Name Condition1 Condition2 Condition3 Condition4 123 ABC 1 1 0 1 245 XYZ 1 0 1 1 789 PQR 0 0 1 1

Which I need to represent as :

 Count % Con1 2 70 Con2 1 34 Con3 2 70 Con4 3 100

I am using mysql and have converted the data as:

 ID Name Condition1 Condition2 Condition3 Condition4 Condition 123 ABC 1 0 0 0 Con1 123 ABC 0 1 0 0 Con2 124 ABC 0 0 0 1 Con4 245 XYZ 1 0 0 0 Con1 245 XYZ 0 0 1 0 Con3 245 XYZ 0 0 0 1 Con4 789 PQR 0 0 1 0 Con3 789 PQR 0 0 0 1 Con4

But for the % calculation, I need the total number of records in beast mode. My beast mode:

Count(Condition)/ sum(count(distinct ID) over ( )

e.g. for Con1 % calculation should be 2/3 whereas when I use window function in beast mode it gives 2/8. Is there nay way to handle it in beast mode or mysql?

• Coach You're close but instead of using a window across the entire dataset you're wanting it across each ID / Name.

Just a minor change to your beast mode should get you what you want.

`Count(Condition)/ sum(count(distinct ID) over (PARTITION BY `ID`)`
**Did this solve your problem? Accept it as a solution!**
• That is giving me 100% for all rows. I added a column just for the beast mode sum(count(distinct ID) over (partition by ID) and it gives same # as condition count.

• Coach Instead of using COUNT try using SUM:

```SUM(Condition1)/ sum(count(distinct ID)) over (PARTITION BY `ID`)

SUM(Condition2)/ sum(count(distinct ID)) over (PARTITION BY `ID`)

SUM(Condition3)/ sum(count(distinct ID)) over (PARTITION BY `ID`)

SUM(Condition4)/ sum(count(distinct ID)) over (PARTITION BY `ID`)```