Header/Detail Join + Sum

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

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

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

ID_TICKET	ID_MALL	PERIOD	FLAG_AMOUNT	STAY_TIME	AMOUNT
TK01	3	201407	0	274	0
TK01	3	201407	1	137	10,74
TK02	3	201408	0	3028	0
TK02	3	201408	1	6056	260,33
TK03	3	201508	1	8844	297,52
TK04	3	201511	1	1263	148,76
TK05	3	201512	0	266	0
TK05	3	201512	1	133	34,71
TK06	3	201602	0	574	0
TK06	3	201602	1	287	34,71
TK07	3	201604	0	339	0
TK08	5	201509	0	1977	0
TK08	5	201509	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!!!

Re: Header/Detail Join + Sum

YoussefBelloum — Wed, 04 Jul 2018 15:26:30 GMT

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 — Wed, 04 Jul 2018 15:33:44 GMT

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..