CODEWITHSUNDEEP
sqloracle-database
Pictionary
Pictionary

Reputation: 13

SQL Combine 2 Different Rows into 1

Let's say I have a table like this, called Inventory:

**Fruit**        **Price**
orange            4
grapefruit        10
banana            15

I would like to write a select statement that would show the output like this:

**Fruit**   **Price**
citrus      14
banana      15

Still new to SQL so I can't figure out the SELECT statement for this. If this is the SELECT statement that would give me the first set of results above (itemized fruit), how can I change it so that it combines oranges and grapefruits and display the result as citrus?

SELECT
INV.fruit,
INV.price
FROM
INVENTORY      INV

Upvotes: 1

Views: 61

Answers (3)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

If citrus is a case which you need to use multiple times, then use WITH clause, also called subquery factoring.

WITH data AS( SELECT (case when fruit in ('orange', 'grapefruit') then citrus else fruit end)) as fruit, sum(price) as price from inventory i group by (case when fruit in ('orange', 'grapefruit') then citrus else fruit end) )......

For more such cases, just include the subqueries in the WITH clause. Finally, select whatever you want from it.

For example, WITH citrus as...., bitter as...etc.

Upvotes: 1

Mureinik
Mureinik

Reputation: 310993

You can use a case expression to translate oranges and grapefruits to citrus:

SELECT   CASE WHEN fruit IN ('orange', 'grapefruit')
              THEN 'citrus' 
              ELSE fruit 
         END,
         SUM(price)
FROM     inventory
GROUP BY CASE WHEN fruit IN ('orange', 'grapefruit')
              THEN 'citrus' 
              ELSE fruit 
         END

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use a case statement:

select (case when fruit in ('orange', 'grapefruit') then citrus else fruit end) as fruit,
       sum(price) as price
from inventory i
group by (case when fruit in ('orange', 'grapefruit') then citrus else fruit end);

Upvotes: 1

Related Questions