Header/Detail Join + Sum

[microwin88x]

Hello,

I have different ID_TICKETs (in TKT_HEADER table) with multiple ID_OPERATIONs (in TKT_DETAIL table).

I need to join both tables, in order to get the following fields on a single table:

ID_TICKET / ID_MALL / PERIOD / FLAG_PAY / STAY_TIME / FLAG_AMOUNT / AMOUNT (without ID_OPERATION)

So I'd have to SUM the STAY_TIME and the AMOUNT for every ID_TICKET, not having all the detail from ID_OPERATIONs.

TKT_HEADER

ID_TICKET	ID_MALL	PERIOD	FLAG_PAY	STAY_TIME
TK01	3	201407	0	137
TK02	3	201408	0	3028
TK03	3	201508	0	2948
TK04	3	201511	0	421
TK05	3	201512	0	133
TK06	3	201602	0	287
TK07	3	201604	0	113
TK08	5	201509	0	659

TKT_DETAIL

ID_TICKET	ID_OPERATION	FLAG_AMOUNT	AMOUNT
TK01	OP01	0	0
TK01	OP02	0	0
TK01	OP03	1	10,74
TK02	OP04	0	0
TK02	OP05	1	247,93
TK02	OP06	1	12,4
TK03	OP07	1	148,76
TK03	OP08	1	29,75
TK03	OP09	1	119,01
TK04	OP10	1	29,75
TK04	OP11	1	84,3
TK04	OP12	1	34,71
TK05	OP13	0	0
TK05	OP14	0	0
TK05	OP15	1	34,71
TK06	OP16	0	0
TK06	OP17	0	0
TK06	OP18	1	34,71
TK07	OP19	0	0
TK07	OP20	0	0
TK07	OP21	0	0
TK08	OP22	0	0
TK08	OP23	0	0
TK08	OP24	0	0
TK08	OP25	1	2,48

The thing is when I try to join both tables by ID_TICKET, I get the following (with STAY_TIME value multiple times):

ID_TICKET	ID_MALL	PERIOD	FLAG_PAY	STAY_TIME	ID_OPERATION	FLAG_AMOUNT	AMOUNT
TK01	3	201407	0	137	OP01	0	0
TK01	3	201407	0	137	OP02	0	0
TK01	3	201407	0	137	OP03	1	10,74
TK02	3	201408	0	3028	OP04	0	0
TK02	3	201408	0	3028	OP05	1	247,93
TK02	3	201408	0	3028	OP06	1	12,4
TK03	3	201508	0	2948	OP07	1	148,76
TK03	3	201508	0	2948	OP08	1	29,75
TK03	3	201508	0	2948	OP09	1	119,01
TK04	3	201511	0	421	OP10	1	29,75
TK04	3	201511	0	421	OP11	1	84,3
TK04	3	201511	0	421	OP12	1	34,71
TK05	3	201512	0	133	OP13	0	0
TK05	3	201512	0	133	OP14	0	0
TK05	3	201512	0	133	OP15	1	34,71
TK06	3	201602	0	287	OP16	0	0
TK06	3	201602	0	287	OP17	0	0
TK06	3	201602	0	287	OP18	1	34,71
TK07	3	201604	0	113	OP19	0	0
TK07	3	201604	0	113	OP20	0	0
TK07	3	201604	0	113	OP21	0	0
TK08	5	201509	0	659	OP22	0	0
TK08	5	201509	0	659	OP23	0	0
TK08	5	201509	0	659	OP24	0	0
TK08	5	201509	0	659	OP25	1	2,48

And finally when I summarize the values I have something like this:

ID_TICKET	ID_MALL	PERIOD	FLAG_PAY	FLAG_AMOUNT	STAY_TIME	AMOUNT
TK01	3	201407	0	0	274	0
TK01	3	201407	0	1	137	10,74
TK02	3	201408	0	0	3028	0
TK02	3	201408	0	1	6056	260,33
TK03	3	201508	0	1	8844	297,52
TK04	3	201511	0	1	1263	148,76
TK05	3	201512	0	0	266	0
TK05	3	201512	0	1	133	34,71
TK06	3	201602	0	0	574	0
TK06	3	201602	0	1	287	34,71
TK07	3	201604	0	0	339	0
TK08	5	201509	0	0	1977	0
TK08	5	201509	0	1	659	2,48

Do you know how could I solve this, to have something like the last table but with the right STAY_TIME and AMOUNT for each ID_TICKET?

Thank you!!!

[YoussefBelloum]

Hi,

you have different ID_operation for every line

that's why it's working with a single STAY_TIME on the last table, because you removed the ID_OPERATION

[YoussefBelloum]

I don't think it is about joining or not, I think it is about keeping the ID_OPERATION column on the chart or not..