CODEWITHSUNDEEP
sqlsql-server
Jacob Hyde
Jacob Hyde

Reputation: 39

How do I split 1 column of data based on the value in a 2nd column in the same table?

I have a column in my database that looks like this; L for Loaded and E for Empty:

Loaded/Empty

with a column in the same table with Mileage:

Mileage

I'm trying to find a way to have it separate and sum() up totals for all Mileage that's Loaded and Empty into 2 columns that would say Total Loaded Mileage and Total Empty Mileage?

Something like this: Total

I've tried a case, self join, and possibly a pivot or view and I can't figure out how to get what I want.

Upvotes: 0

Views: 45

Answers (3)

Deepak Bhise
Deepak Bhise

Reputation: 171

Easy way with normal sql is as below.

SELECT 
SUM(CASE WHEN loaded = 'L' THEN move_distance ELSE 0 END) as 
LoadedMoveDistance
SUM(CASE WHEN loaded = 'E' THEN move_distance ELSE 0 END) as 
LoadedMoveDistance
FROM tablename

other way is to use Pivot which is SQL server specific it will something as below. 

select [L] as Move_distance_loaded, [E] as Move_distance_empty
from
(
Select loaded, move_distance from table
) tb
PIVOT
(
SUM(move_distance)
FOR loaded IN ([L], [E])
) As pvt

you need to try pivot thing, above code will not work as exactly provided.

Upvotes: 0

Charlie K
Charlie K

Reputation: 114

Simplest in my experience is create this query as a union:

Select Loaded, move_distance Move_Loaded, 0 Move_Empty
from mytable
where Loaded = 'L'
UNION
Select Loaded, 0 , move_distance
from mytable
where Loaded = 'E'

If you want just the sums use:

Select Loaded, sum(move_distance) Move_Loaded, 0 Move_Empty
from mytable
where Loaded = 'L'
group by Loaded, 0
UNION
Select Loaded, 0 , sum(move_distance)
from mytable
where Loaded = 'E'
group by Loaded, 0

You might be able to leave out the "0" from your group by lines.

Upvotes: 0

LSerni
LSerni

Reputation: 57428

You could try an IF (actually IIF in SQL Server):

COALESCE(SUM(IF(loaded = 'L', move_distance, 0)),0) AS when_loaded,
COALESCE(SUM(IF(loaded = 'E', move_distance, 0)),0) AS when_empty

The COALESCE allows for the pathologic case when the table is empty (or any additional WHERE yields no matches), and the SUM would return NULL.

Or CASE:

COALESCE(SUM(CASE WHEN loaded = 'L' THEN move_distance ELSE 0 END), 0)

Upvotes: 1

Related Questions