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
- 10.5K All Categories
- 5 Connect
- 915 Connectors
- 250 Workbench
- 459 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 186 Visualize
- 250 Beast Mode
- 2.1K Charting
- 11 Variables
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 20 Manage
- 41 Governance & Security
- 170 Product Ideas
- 1.2K Ideas Exchange
- 10 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive