Issue getting Last_Day(`Date`) working properly in mysql query
Hi,
I have a MySql dataflow that I'm having an issue with. I have this query that I'm working with and I'm trying to pull the latest NumProjects value in each month. In the output, I'm seeing that its showing the latest set of values twice because the values are different (I assume). This winds up throwing my chart off and summing the two numbers together which misrepresents the intended value. Any ideas on how to fix this would be greatly appreciated.
SELECT `NumProjects`, Last_Day(`Date`) AS 'Date', `PhaseName`
FROM `pmat_historical_all_appended`
GROUP BY `PhaseName`, `Date`
ORDER BY `Date` desc
Best Answer
-
I think you need to change your grouping. Try this:
SELECT `NumProjects`, Last_Day(`Date`) AS 'Date', `PhaseName`
FROM `pmat_historical_all_appended`
GROUP BY `PhaseName`, Last_Day(`Date`)
ORDER BY `Date` descDoes that give you the result you're looking for?
Or you might could try:
SELECT `NumProjects`, `Date`, `PhaseName`
FROM `pmat_historical_all_appended`
WHERE `Date` IN (SELECT DISTINCT LAST_DAY(`Date`) FROM `pmat_historical_all_appended`)
ORDER BY `Date` descSincerely,
Valiant
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.0
Answers
-
I think you need to change your grouping. Try this:
SELECT `NumProjects`, Last_Day(`Date`) AS 'Date', `PhaseName`
FROM `pmat_historical_all_appended`
GROUP BY `PhaseName`, Last_Day(`Date`)
ORDER BY `Date` descDoes that give you the result you're looking for?
Or you might could try:
SELECT `NumProjects`, `Date`, `PhaseName`
FROM `pmat_historical_all_appended`
WHERE `Date` IN (SELECT DISTINCT LAST_DAY(`Date`) FROM `pmat_historical_all_appended`)
ORDER BY `Date` descSincerely,
Valiant
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.0 -
Thank you. I'm not sure why I didn't try that myself but your first solution worked perfectly.
0 -
Glad to hear it! We all need a 2nd pair of eyes on our code sometimes.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 58 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive