Calculating total against subtotal

user016969

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?

GrantSmith

Hi @user016969 

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`)

user016969

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.

 

 

GrantSmith

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`)

user016969

That's giving 0%

user016969

In the absence of other solution, through mysql I added a static column in table to store the total number of unique records and using that value in beast mode.