Stuck with SQL summation

Question

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 3-Jan-14 22:27pm

abhitechno

Updated 4-Jan-14 1:12am

v3

Add a Solution

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Christian Graus · Answer 1 · 2014-01-04T00:07:00

Solution 2

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.

Posted 4-Jan-14 0:07am

Christian Graus

Updated 4-Jan-14 0:08am

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

Maciej Los · Answer 2 · 2014-01-03T23:48:00

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