Please, have a look at example:
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:
CREATE TABLE TableName (
hiy_hno INT,
RouteID INT,
GIOA1MC VARCHAR(30),
FWM_ADL1 INT
)
You can transfer data from existing table in that way:
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:
SELECT SUM(FWM_ADL1) AS SumOfF20001
FROM TableName
WHERE GIOA1MC = 'F20001'
For other values:
SELECT GIOA1MC, SUM(FWM_ADL1) AS SumOfGIOA1MC
FROM TableName
GROUP BY GIOA1MC