Click here to Skip to main content
15,901,284 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table that looks somewhat like this

hiy_hno, GIO_A1MC_1, GIO_A1MC_2,GIO_A1MC_3, GIO_A1MC_4, GIO_A1MC_5,FWM_ADL1_1, FWM_ADL1_2, FWM_ADL1_3, FWM_ADL1_4, FWM_ADL1_5
13312928 F20001     F10002 F10003 A00008 A00008 1200 0 0 0 0 
13313226 F10002     F10003 A00008 A00008 F20001 0    0 0 0 1198 
13313126 F20001     F10002 F10003 A00008 A00008 1102 0 0 0 0 
13312793 F20001     F10002 F10003 A00008 A00008 1190 0 0 0 0 
13312834 F20001     F10002 F10003 A00008 A00008 1007 0 0 0 0 

Now hiy_hno is the key field. I need to find the sum of all quantities of "F20001" element. The element identifier may be in any one of GIO_A1MC_x fields and its corresponding value in FWM_ADL1_x field. The problem is illustrated in 1st and 2nd row where the element "F20001" gets placed in different columns and hence their corresponding quantities is also placed in different columns.

Answers are free to include any necessary changes in table structure but with cognizance to the fact that order of elements(and their corresponding quantities) will always remain variable.

Thanks in advance.

P.S. - Guys this is actually data coming from various material bins and the quantity of element released from them.There are 20 such bins, so "sum(some sort of case logic)" wont be an elegant solution.
Posted
Updated 4-Jan-14 1:12am
v3
Comments
Taha Akhtar 4-Jan-14 4:53am    
try
sum(case when [columnName]=F10002 then [your summation column] end)
abhitechno 4-Jan-14 7:03am    
no, this will work, but will get extremely cumbersome to generate consumption report for even 1 material with 20 possible bins. forget about even getting a report for 10 materials stored in 20 bins.
Maciej Los 4-Jan-14 5:34am    
Please be more specific and provide more details if you want to get help. What's for these fields: GIO_A1MC_x and FWM_ADL1_x? Why do you store some data in a several fields?
abhitechno 4-Jan-14 7:01am    
this data is about material_code(s) of different storage bins and the quantity of element released from them. the problem is that any material can be stored in any bin (bin 1 to 5). as a material is changed, the material_code corresponding to that bin is updated. the situation is dynamic and hence assigning a particular material fixedly to a bin is impossible.
Maciej Los 4-Jan-14 7:15am    
See my answer, please.

SQL
select sum (
  case
    when GIO_A1MC_1 = 'F20001' then FWM_ADL1_1
    when GIO_A1MC_2 = 'F20001' then FWM_ADL1_2
    when GIO_A1MC_3 = 'F20001' then FWM_ADL1_3
    when GIO_A1MC_4 = 'F20001' then FWM_ADL1_4
    when GIO_A1MC_5 = 'F20001' then FWM_ADL1_5
    else 0
    end)
    from t2


This assumes the value can't appear twice in the same row. If it can, then you could use 5 CTEs to aggregate each set of values.
 
Share this answer
 
v2
Comments
Maciej Los 4-Jan-14 6:12am    
Twice - it's not possible ;) Have a look at the data. Other fields are equal to zero ;)
Christian Graus 4-Jan-14 6:13am    
If they are always zero, then he can just sum all five fields. But, he seems to imply that is not enough.....
abhitechno 4-Jan-14 7:04am    
no no they maynot be zero.
Amir Mahfoozi 4-Jan-14 6:44am    
+5
Please, have a look at example:
SQL
CREATE TABLE #tmp (	hiy_hno INT, GIO_A1MC_1 VARCHAR(30), GIO_A1MC_2 VARCHAR(30),
		GIO_A1MC_3 VARCHAR(30), GIO_A1MC_4 VARCHAR(30), GIO_A1MC_5 VARCHAR(30),
		FWM_ADL1_1 INT, FWM_ADL1_2 INT, FWM_ADL1_3 INT,
		FWM_ADL1_4 INT,FWM_ADL1_5 INT)

INSERT INTO #tmp (hiy_hno, GIO_A1MC_1, GIO_A1MC_2,GIO_A1MC_3, GIO_A1MC_4, GIO_A1MC_5,FWM_ADL1_1, FWM_ADL1_2, FWM_ADL1_3, FWM_ADL1_4, FWM_ADL1_5)
VALUES (13312928, 'F20001', 'F10002', 'F10003', 'A00008', 'A00008', 1200, 0, 0, 0, 0),
(13313226, 'F10002', 'F10003', 'A00008', 'A00008', 'F20001', 0, 0, 0, 0, 1198),
(13313126, 'F20001', 'F10002', 'F10003', 'A00008', 'A00008', 1102, 0, 0, 0, 0), 
(13312793, 'F20001', 'F10002', 'F10003', 'A00008', 'A00008', 1190, 0, 0, 0, 0), 
(13312834, 'F20001', 'F10002', 'F10003', 'A00008', 'A00008', 1007, 0, 0, 0, 0)


SELECT hiy_hno, SUM(COALESCE(FWM_ADL1_1, FWM_ADL1_2, FWM_ADL1_3, FWM_ADL1_4, FWM_ADL1_5)) AS SUM_FWM_ADL_x
FROM #tmp
WHERE COALESCE(GIO_A1MC_1, GIO_A1MC_2,GIO_A1MC_3, GIO_A1MC_4, GIO_A1MC_5) = 'F20001'
GROUP BY hiy_hno

DROP TABLE #tmp


Result:
hiy_hno		SUM_FWM_ADL_x
13312793	1190
13312834	1007
13312928	1200
13313126	1102


As you can see, value 'F20001' is stored everywhere!

You need to provide more details about your issue if you want our help.


I think your table should looks like:
SQL
CREATE TABLE TableName (
    hiy_hno INT,
    RouteID INT,
    GIOA1MC VARCHAR(30),
    FWM_ADL1 INT
)


You can transfer data from existing table in that way:
SQL
INSERT INTO TableName (hiy_hno, RouteID, GIOA1MC, FWM_ADL1)
SELECT hiy_hno, RouteID, GIOA1MC, FWM_ADL1
FROM (
	SELECT hiy_hno, 1 AS RouteID, GIO_A1MC_1 AS GIOA1MC, FWM_ADL1_1 AS FWM_ADL1
	FROM #tmp
	UNION ALL
	SELECT hiy_hno, 2 AS RouteID, GIO_A1MC_2 AS GIOA1MC, FWM_ADL1_2 AS FWM_ADL1
	FROM #tmp
	UNION ALL
	SELECT hiy_hno, 3 AS RouteID, GIO_A1MC_3 AS GIOA1MC, FWM_ADL1_3 AS FWM_ADL1
	FROM #tmp
	UNION ALL
	SELECT hiy_hno, 4 AS RouteID, GIO_A1MC_4 AS GIOA1MC, FWM_ADL1_4 AS FWM_ADL1
	FROM #tmp
	UNION ALL
	SELECT hiy_hno, 5 AS RouteID, GIO_A1MC_5 AS GIOA1MC, FWM_ADL1_5 AS FWM_ADL1
	FROM #tmp
) AS T	
ORDER BY hiy_hno, RouteID


Then you'll be able to use query like that:
SQL
SELECT SUM(FWM_ADL1) AS SumOfF20001
FROM TableName
WHERE GIOA1MC = 'F20001'

For other values:
SQL
SELECT GIOA1MC, SUM(FWM_ADL1) AS SumOfGIOA1MC
FROM TableName
GROUP BY GIOA1MC
 
Share this answer
 
v2
Comments
Christian Graus 4-Jan-14 6:12am    
See my answer :-) coalesce won't work here, because the other values are not null. It will just always return the first value. I assume as hiy_hno is always different, he wants the sum across all values, not for each.
Maciej Los 4-Jan-14 6:14am    
I saw it and i posted a comment to your answer ;)
COLAESCE works. It's a trick ;)
Christian Graus 4-Jan-14 6:15am    
So coalesce works differently to the documentation ? "Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL."
Maciej Los 4-Jan-14 6:20am    
No, i'm saying that i used it in magic way ;)
abhitechno 4-Jan-14 7:08am    
there's no magic in the way u used coalesce. it will generate a wrong report.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900