I have this table structure for table prices
:
CREATE TABLE prices
(
id int,
priceFrom int,
priceUp int
);
INSERT INTO prices (id, priceFrom, priceUp)
VALUES (1, 23, 23), (2, 0, 0), (3, 12, 13),
(4, 40, 40), (5, 15, 15), (6, 0, 0);
This is the result:
I have this query:
select
pricefrom, priceup,
case
when pricefrom = 0 then null
when priceFrom <> priceUp then priceFrom + ' - ' + priceUp
when priceFrom = priceUp then priceFrom
end as FinalPrice
from
prices
what I need is to do a case when
but in my query in this line:
I try to do this with <>
but in the result appears the sum for both numbers:
How can I fix this?
CASE must include the following components: WHEN , THEN , and END . ELSE is an optional component. You can make any conditional statement using any conditional operator (like WHERE ) between WHEN and THEN . This includes stringing together multiple conditional statements using AND and OR .
CASE statement in SQL and aggregate functions Aggregate functions in SQL Server perform calculations and return a single value. Examples of aggregate functions are MIN, MAX, COUNT, ABG and CHECKSUM. For this purpose, we use the COUNT aggregate function in SQL Server.
Of course I can write the case condition multiple times, each time return one value. However, as I have many condition need to fit, say 100. It is not good to repeat case condition again and again.
Here, each WHEN statement has its Conditional Boolean expression. Each Boolean expression i.e. Tutorial_name = 'SQL',… is evaluated for TRUE/FALSE until first Boolean expression which evaluates to TRUE. CASE keyword is immediately followed by CASE_Expression and before WHEN statement.
The SQL CASE Statement The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
The SQL Server CASE statement ends with the END clause. Let’s now see the CASE statement in action. In a previous section, we created a table named Cars inside the ShowRoom database. The condition column had the value X for all rows.
If the CONDITION returns true the value that follows the THEN clause is stored in columnX. Else, the value after the ELSE clause, will also be stored in columnX. The SQL Server CASE statement ends with the END clause.
I think you're looking for the concat function here.
select pricefrom, priceup,
case
when pricefrom = 0 then null
when priceFrom <> priceUp then concat(priceFrom, ' - ', priceUp)
when priceFrom = priceUp then cast(priceFrom as varchar(8))
end as FinalPrice
from prices
This link will probably be helpful
MySQL combine two columns into one column
You have to CAST
to VARCHAR
:
select pricefrom, priceup,
case
when pricefrom = 0 then null
when priceFrom <> priceUp then concat(cast(priceFrom as varchar(8)),
' - ',
cast(priceUp as varchar(8)))
when priceFrom = priceUp then cast(priceFrom as varchar(8))
end as FinalPrice
from prices
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With