MySQL newbie question
I have a dataflow I've built in Magic ETL, and I want to add a new column using a conditional statement to return the value from one of 3 existing columns (doing this in Beast Mode prevents me from getting the correct aggregations, so I'm trying to add in the dataflow itself).
So I want to have Invoice Sales returned if >0, if not then return j_quote_ if >0, if not then return WP Price. The Magic ETL dataflow basically looks like this, and I want to add the column in bold:
Job No_1 | J_JOB_STATUS | Invoice Sales | WP Price | J_QUOTE_ | Total Job |
123456 | JOB INVOICED. INVOICE# 12345678 | 2992.5 | 3021.02 | 0 | 2992.5 |
123457 | JOB ENTERED | 340 | 0 | 340 | |
123458 | JOB CARD PRINTED | 6196 | 6240 | 6240 |
This is the syntax I have in MySql, but I'm a newbie at SQL, and I'm sure I'm missing something simple. Any suggestions are welcome:
Thanks!
Best Answer
-
Here you go, this should do the trick.
SELECT *,
CASE WHEN `Invoice Sales` > 0 THEN `Invoice Sales`
WHEN `Invoice Sales` < 0 AND `J_QUOTE_` > 0 THEN `J_QUOTE_`
ELSE `WP Price`
END AS 'Total Job'
FROM sales_wp_quote_summarized_by_job
Sincerely,
ValiantSpur
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.2
Answers
-
Here you go, this should do the trick.
SELECT *,
CASE WHEN `Invoice Sales` > 0 THEN `Invoice Sales`
WHEN `Invoice Sales` < 0 AND `J_QUOTE_` > 0 THEN `J_QUOTE_`
ELSE `WP Price`
END AS 'Total Job'
FROM sales_wp_quote_summarized_by_job
Sincerely,
ValiantSpur
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.2 -
Excellent. Thanks.
0
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.2K Connectors
- 284 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.9K Transform
- 89 SQL DataFlows
- 556 Datasets
- 2.2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 569 Beast Mode
- 10 App Studio
- 27 Variables
- 578 Automate
- 140 Apps
- 414 APIs & Domo Developer
- 23 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 350 Distribute
- 90 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 92 Manage
- 89 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 85 Community Announcements
- 4.8K Archive