DOMO SQL MAX(date) not working
hello, hope someone could help on my SQL:
I just created a simple SQL under DOMO MySQL to select some fields and the MIN and MAX of a date, like:
SELECT `id`, `name`, min(`a_date`) as 'oldest date', max(`a_date`) as 'latest date'
FROM`data_1`
GROUP BY `id`, `name`
but nothing is returned in the 2 date fields, original dataset contain a valid date value in all rows.
I tried to simply select the MIN or MAX date without GROUP BY, it works properly, but it returns null once with GROUP BY. Any idea and solution?
thank you!
Answers
-
@Nek , Domo MySQL dataflows run on MySQL 5.6 databases. If you write valid MySQL code it should run.
Your code looks fine so I'm surprised you're not getting results.
Keep in mind the preview will only show the first 100? rows AND default behavior when building dataflows is for domo to only ingest the first 10k? rows, so maybe in the set of data you're aggregating there are no dates for that particular ID and Name.
Try
SELECT Count(*) FROM `table` WHERE a_date is not null and id is not null and name is not null
Then
SELECT `id`, `name`, min(`a_date`) as 'oldest date', max(`a_date`) as 'latest date' FROM`data_1` where a_date is not null GROUP BY `id`, `name`
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
thanks jaeW_at_Onyx for your reply and advice! I did check all records contain a valid date, also tried adding:
where a_date is not null
or
where a_date '1900-01-01'
but no luck... i just process it ahead and see what the actual outcome would be
besides, I created the same with ETL, in preview, I can find a date but not get the correct date, e.g. a case with oldest date in 2017 and latest date in 2019, both my created min and max columns show date in 2019...
not sure if i cannot do both min and max on date at once!?
thanks.
0 -
You might try forcing a convert to the date data type to make sure MySQL is properly reading them as dates.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive