How to use partition by clause in Domo SQL
I have following columns:
Kingdom,
Animal,
Age,
Weight
Each row can be repeated multiple times. I want to sum values on the kingdom level in another view/table:
My code is:
SELECT
'Kingdom',
SUM (MAX ('Age')) OVER (PARTITION BY 'Animal') AS 'Age',
SUM(MAX ('Weight')) OVER (PARTITION BY 'Animal') AS 'Weight'
FROM 'Table'
GROUP BY 'Kingdom'
The Domo transform does not work. Says "Whoops! Something went wrong"
Can someone help me as to how to make this work?
The
Best Answer
-
Yes and no...
You can use windowed functions in a redshift dataflow. But not in the standard MySQL dataflow option.
If you aren't familiar with redshift, then you will need to study up on some of the differences because it uses some different syntax.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman2
Answers
-
Hi @hamza_123 ,
The query that you have written has a syntax error.
SUM (MAX ('Age')) OVER (PARTITION BY 'Animal') AS 'Age',
SUM(MAX ('Weight')) OVER (PARTITION BY 'Animal') AS 'Weight'Aggregation of already aggregated column (SUM(COUNT)) can not be done this way.
Also, It would be great to check with Domo Support if MySQL flow currentlu supports windoe aggregation or not. I doubt it does not. Still it's advised to check with Domo.
Moreover, window aggregation is supported in beast modes calculation, so you can directly place your clculation in the cards' beast mode.
Hope this helps.
Regards
Aditya Jain
'Happy to Help'1 -
I would suggest the following steps in a SQL dataflow:
SELECT
`Kingdom`,
`Animal',
MAX(`Age`) as `Max Age`,
MAX(`Weight`) as `Max Weight`
FROM 'Table'
Group By 1,2This will give you a table with:
Kingdom,
Animal,
Max Age,
Max Weight
Then you can get your desired output table:
SELECT
`Kingdom`,
SUM(`Max Age`) as `Age`,
SUM(`Max Weight`) as `Weight`
FROM 'Table_2'
Group By `Kingdom`
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
Thank you for the reply. So I'm guessing partitions do not work in Domo SQL?
0 -
Yes and no...
You can use windowed functions in a redshift dataflow. But not in the standard MySQL dataflow option.
If you aren't familiar with redshift, then you will need to study up on some of the differences because it uses some different syntax.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman2 -
Thankyou for the reply. It is clear now.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive