Calculating total against subtotal

user016969 · 2020-09-02T18:12:43+00:00

Hi, I have a data set as follows: IDNameCondition1Condition2Condition3Condition4123ABC1101245XYZ1011789PQR0011 Which I need to represent as : Count%Con1270Con2134Con3270Con43100 I am using mysql and have converted the data as:…

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?

Find more posts tagged with

Comments

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.