SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Number events in the line

    Hi all.

    I have this two tables:

    doTable_A
    Code:
    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
    Code:
    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:

    Code:
    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

  2. #2
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't explain in the right way ?
    I need to count the rows more than three lines.
    Any suggestions?

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    doTable_A
    Code:
    ES	DATE		LINE
    DM40	2010-02-06	DM4047608 
    DM40	2010-02-10	DM4047608 
    DM40	2010-02-10	DM4047608
    doTable_B
    Code:
    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:

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

  5. #5
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I try this and working:

    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •