CODEWITHSUNDEEP
sqlsql-server
Freakishly
Freakishly

Reputation: 1571

How do I add another column to a SQL table based on rows grouped together

I have a table that looks like this:

DATE        ITEMS       CLASS       CATEGORY
---------------------------------------------------------
2016-10-01  17915       Red         Apartment
2016-10-01  39246       Red         Complex
2016-10-01  4376        Blue        Apartment
2016-10-01  12668       Blue        Complex
2016-10-01  513         Yellow      Apartment
2016-10-01  23271       Yellow      Complex

I want to add another column to this table that groups by the CLASS and calculates one value as a percentage of the other. For instance, in the above case, for the two rows marked as Class = Red, I want to take the value of Items in the Apartment category (17915) and calculate it as a percentage of the value of Items in the Complex category (39246), which works out to 49.64%, which is about 50%.

So the query applied to the table above should produce an output table that looks like this:

DATE        ITEMS       CLASS       CATEGORY    OCCUPANCY
--------------------------------------------------------------------------
2016-10-01  17915       Red         Apartment   17915 * 100 / 39246
2016-10-01  39246       Red         Complex     null
2016-10-01  4376        Blue        Apartment   4376 * 100 / 12668
2016-10-01  12668       Blue        Complex     null
2016-10-01  513         Yellow      Apartment   513 * 100 / 23271
2016-10-01  23271       Yellow      Complex     null

Not those operations, but the actual values. What would the query for this look like? I tried variations of the GROUP BY clause but I cannot think of anything that would compare one row against the next, after the grouping.

Upvotes: 1

Views: 54

Answers (3)

Damian
Damian

Reputation: 74

This query makes a few assumptions. 1. Your categories will always Apartment and Complex. 2. This is SQL Server 2012 (you tagged the question with sql server). I am not sure if this will work with MySql. Just throwing out an alternative. 

SELECT 
    DATE, 
    ITEMS, 
    CLASS, 
    CATEGORY, 
   (ITEMS * 100)/ LEAD(ITEMS) OVER (PARTITION BY CLASS ORDER BY CATEGORY) AS OCCUPANCY 
FROM Your_Table

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a self join and a union

select  a.DATE,  a.ITEMS, a.CLASS, a.CATEGORY, (a.ITEMS *100)/ b.ITEMS  AS OCCUPANCY
from my_table as a
inner join my_table as b on a.class = b.classe 
wheer a.CATEGORY = 'Apartment'
AND b.CATEGORY = 'Complex'
UNION 
SELECT  DATE,  ITEMS, CLASS, CATEGORY, NULL
FROM my_table 
where CATEGORY = 'Complex'

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

This is a way to do it in MySQL, assuming each Class always has categories Apartment and Complex.

select ta.date,ta.items,ta.class,ta.category,ta.items*100.0/tc.items occupancy
from t ta
join t tc on ta.date=tc.date and ta.class=tc.class 
and ta.category='Apartment' and tc.category='Complex'
union all  
select date,items,class,category,null
from t
where category='Complex'

In SQL Server, you can do it using window function max.

select date,items,class,category,
case when category = 'Apartment' then
max(case when category='Apartment' then items end) over(partition by date,class)*100.0
/max(case when category='Complex' then items end) over(partition by date,class)
end as occupancy
from t

Upvotes: 0

Related Questions