Freakishly Freakishly - 28 days ago 5
SQL Question

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.

Answer

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'