How to use a partition by in a table with a clause from another table

Transact-SQL
1

hello,

Recently I received a complicated task (or at least for me it is ) and i tried to complete it but i could'nt 100%.

I have 2 tables: Country and Mapping:

703×307 7.48 KB

I need to fill the "MY Column" column as it is the above print screen.

I have to divide Value2 to Value1 into the "MY Column" column based on some criteria: grouping by country and product but also taking into consideration the top of every country from the Mapping table.

For example, Albania has 2 types of Products: Other Compute and Modular Smart, For Other Compute I need to make the division of Value2 to Value1 taking into consideration the Top which is 10 (in mapping table);( 10 means all the Tops in Country table less or equal to 10; 5 means all the Tops in Country table equal or less than 5). So for Albania-Other Compute will be: sum(34+65+76)/sum(23+34+5)=2.82

Albania-Modular Smart: I have here Top =2 and Top=12. Because Albania in the Mapping table has Top=10 then I need to do only the 89/65=1.37

Germany-Other Compute. In the Mapping table, Germany has Top=5 and in my Country table, for this product, I only have Top=11 so here I don't need to do this division

Germany-Store Once: sum(21+6)/sum(34+45)

etc

I tried to use partition by clause by making additional columns in which I make the sum partitioned by country and product but I don't know how to link it with the Mapping table so it knows for which rows I can make this sum

I wrote this:

select sum(Value1) over(partition by [Country].[Product]]) as column1,

select sum(Value2) over(partition by [Country].[Product]]) as column2,

' ' as [MY column]

from Country

The problem is that makes the sum of all the values and not only for the values that match the Top from the Mapping table.

I have no idea how to join it with the Mapping table so I can see for what rows I have to do the division.

I wanted to make the division in "MY column' from column2/column1. I know it's more work but I have no other idea on how to do it easier.

this is the sample table:

create table Country

([Country] nvarchar(100)null,

[Product] nvarchar(100)null,

[Top] int null,

[Value1]float null,

[Value2]float null,

[Final_Column] float null)

insert into Country([Country],[Product],[Top],[Value1],[Value2],[Final_Column])

values('Albania','Other Compute','1','23','34','')

('Albania','Other Compute','1','34','65','')

('Albania','Other Compute','2','5','76','')

('Albania','Modular Smart','2','65','89','')

('Albania','Modular Smart','12','10','90','')

('Germany','Other Compute','11','89','87','')

('Germany','StoreOnce','12','90','11','')

('Germany','StoreOnce','3','34','21','')

('Germany','StoreOnce','6','12','4','')

('Germany','StoreOnce','5','45','6','')

('Germany ','Software','2','89','87','')

('Germany','Software','7','90','11','')

('France','Software','24','11','22','')

create table Mapping

([Country] nvarchar(100)null,

[Top] int null)

insert into Mapping ([Country],[Top])

values('Albania','10')

('Germany','5')

('France','10')

I would highly appreciate it if someone could help me :slight_smile:

2

No obvious PK.
Do not understand your result for Germany, Software.

The following should get you started:

SELECT C.Country, C.Product, C.[Top], C.Value1, C.Value2
	,CASE
		WHEN M.[Top] IS NOT NULL
		THEN
			ROUND
			(
				SUM(CASE WHEN M.[Top] IS NOT NULL THEN C.Value2 ELSE 0.0 END) OVER (PARTITION BY C.Country, C.Product)
				/
				SUM(CASE WHEN M.[Top] IS NOT NULL THEN C.Value1 ELSE 0.0 END) OVER (PARTITION BY C.Country, C.Product)
			, 2)
		ELSE NULL
	END AS MyColumn
FROM Country C
	LEFT JOIN Mapping M
		ON C.Country = M.Country
			AND C.[Top] <= M.[Top];

or to update:

WITH MyCols
AS
(
	SELECT C.Country, C.Product, C.[Top], C.Value1, C.Value2, C.Final_Column
		,CASE
			WHEN M.[Top] IS NOT NULL
			THEN
				ROUND
				(
					SUM(CASE WHEN M.[Top] IS NOT NULL THEN C.Value2 ELSE 0.0 END) OVER (PARTITION BY C.Country, C.Product)
					/
					SUM(CASE WHEN M.[Top] IS NOT NULL THEN C.Value1 ELSE 0.0 END) OVER (PARTITION BY C.Country, C.Product)
				, 2)
			ELSE NULL
		END AS MyColumn
	FROM Country C
		LEFT JOIN Mapping M
			ON C.Country = M.Country
				AND C.[Top] <= M.[Top]
)
UPDATE MyCols
SET Final_Column = MyColumn;

select * from Country;
1 Like
3

thank you so much, it helped me get started :slight_smile: