Number events in the line

[viki1967]

Hi all.

I have this two tables:

doTable_A

ES	DATE		LINE
DM40	06/02/2010	DM4040001 
DM40	04/02/2010	DM4040417 
DM40	06/02/2010	DM4040419 
DM40	10/02/2010	DM4040606 
DM40	09/02/2010	DM4040652 
DM40	10/02/2010	DM4040652 
DM40	09/02/2010	DM4040661 
DM40	04/02/2010	DM4041401 
DM40	10/02/2010	DM4047606 
DM40	10/02/2010	DM4047607 
DM40	06/02/2010	DM4047608 
DM40	10/02/2010	DM4047608 
DM40	10/02/2010	DM4047608 
DM40	01/02/2010	DM4047901 
DM40	10/02/2010	DM4048110 
DM40	09/02/2010	DM4048204 
DM40	05/02/2010	DM4049409

doTable_B

ES	DATE_EVENT	LINE_MT
VT	01/02/2010	DM4040303-P.ROMA
VT	31/01/2010	DM4041100-DECRISTOFO
VT	03/02/2010	DM4042905-S.LORENZO
VT	04/02/2010	DM4043608-MURACCE
VT	05/02/2010	DM4043807-M.RAZZANO
VT	31/01/2010	DM4045402-PUCCI
VT	31/01/2010	DM4045402-PUCCI
VT	01/02/2010	DM4045501-SEMOVENTI
VT	06/02/2010	DM4045501-SEMOVENTI
VT	05/02/2010	DM4045509-MONTE ORO
VT	06/02/2010	DM4045912-FORTUNA
VT	04/02/2010	DM4047608-FEEDER
VT	04/02/2010	DM4047608-FEEDER
VT	04/02/2010	DM4047608-FEEDER
VT	05/02/2010	DM4047608-FEEDER
VT	03/02/2010	DM4048706-MUGNAINI
VT	03/02/2010	DM4048706-MUGNAINI

I need this output:

line_a+b		count
DM4047608-FEEDER	7

Because in the two tables the number events of the LINE DM4047608-FEEDER is > 3.


Can you help me?
Kind regards

[viki1967]

I don't explain in the right way ?
I need to count the rows more than three lines.
Any suggestions?

[guelphdad]

Why is the count 7? How do the fields match up? On the numeric values in your first table and the matching part in the second table?

Could there also be values in the second table:
DM4047608-FEEDER
DM4047608-SLIPPERS

and the second value there get counted too? or is the text part always the same?

What have you tried so far?

Do you understand how UNION and UNION ALL work and are different from each other?

Also for your date columns it looks like you are using a varchar/char field type, why not use them as DATE types?

[viki1967]

doTable_A

ES	DATE		LINE
DM40	2010-02-06	DM4047608 
DM40	2010-02-10	DM4047608 
DM40	2010-02-10	DM4047608

doTable_B

ES	DATE_EVENT	LINE_MT
VT	2010-02-04	DM4047608-FEEDER
VT	2010-02-04	DM4047608-FEEDER
VT	2010-02-04	DM4047608-FEEDER
VT	2010-02-05	DM4047608-FEEDER

Output:

line_a+b		count
DM4047608-FEEDER	7

DM4047608-FEEDER
DM4047608-SLIPPERS

It's not possible because the line DM4047608 always is FEEDER.
I don't try anything...
Date columns it's as DATE types: YYYY-MM-DD.

thanks x your answer.

[viki1967]

I try this and working:

select substring_index(line,'-',1) as rad,count(substring_index(line,'-',1)) as q from (
   select line from doTable_A
   union all
      select line_mt from doTable_B
) as tab
   group by rad
   having q > 3