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?
Comments
-
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`)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
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.
0 -
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`)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
That's giving 0%
0 -
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.
0
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Data Connections
- 1.3K Connectors
- 309 Workbench
- 17 Cloud Integrations
- Data & ETL
- 2.3K Magic ETL
- 120 SQL DataFlows
- 667 Datasets
- Visualize & Apps
- 90 App Studio
- 198 Pro-code Components
- 2.6K Charting & Analyzer
- 873 Calculations & Variables (Beast Mode)
- AI & Data science
- 23 Domo AI & AI Chat
- 4 Managing AI
- 18 Jupyter Workspaces
- Automate
- 122 Workflows
- Alerts
- Distribute
- 118 Domo Everywhere
- 284 Reporting
- Manage
- 146 Governance & Security
- 489 APIs
- 11 Add-ins & Plugins
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 118 Community Announcements
- 5K Archive