DISTINCT AND IF(SUM)

[PARK_JY]

Hi,

I want to solve this problem.

TABLE1:

LOAD

LINE	OK	NG
A_LINE	1
B_LINE	1
A_LINE	1	1
A_LINE	1
B_LINE		1
A_LINE		1
B_LINE	1

I want to change the above chart to look like the below chart in script.

LINE	SUM_OK	SUM_NG
A_LINE	3	2
B_LINE	2	1

I wrote this but it didn't work.

TABLE2 :

LOAD

Distinct(LINE),

SUM(IF(LINE=LINE), OK) as SUM_OK,

SUM(IF(LINE=LINE),  NG) as SUM_NG

RESIDENT TABLE #1;

Please tell me how to solve.

Thank you.

[MayilVahanan · Accepted solution]

Hi

Try like below

LOAD

LINE,

SUM(OK) as SUM_OK,

SUM(NG) as SUM_NG

RESIDENT TABLE #1

group by LINE;

[Lisa_P]

For your second table, add this:

AggTable:
Load LINE,
Sum(OK) as SUM_OK,
Sum(NG) as SUM_NG
Resident Table1
Group by LINE;

[PARK_JY]

Thank you!!