SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    SQL COUNT and SUM for month

    Hi everyone, I need your help.

    I have this tables in DB MySQL:

    TBL_A
    Code:
    NAME	DATE
    W	2009-09-01
    Y	2009-09-02
    X	2009-09-04
    Z	2009-10-01
    TBL_B
    Code:
    NAME	DATE		AV
    W	2009-09-02	10
    Y	2009-09-03	15
    X	2009-09-17	15
    Z	2009-10-08	3
    I need this output:

    Code:
    tot_NAME				tot_AV
    ( TBL_A + TBL_B 
    WHERE month = 09 )			( WHERE month = 09 )
    
    6					40
    Can you help me?
    Many thanks.
    Mike

  2. #2
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    528
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    what do you mean by tot_NAME? did you want to count the names? or maybe count the distinct names?

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    There are multiple ways of getting that result

    Code:
    select count(*) + (select count(*) 
                         from tbl_a 
                        where date between date '2009-09-01' 
                                       and date '2009-09-30') as tot_NAME,
           sum(av) as tot_AV
      from tbl_b
     where date between date '2009-09-01' 
                    and date '2009-09-30'
    or

    Code:
    select count(*) as tot_NAME, 
           sum(av) as tot_AV
      from (select name,
                   0 as av
              from tbl_a
             where date between date '2009-09-01' and date '2009-09-30'
             union
            select name,
                   av
              from tbl_b
             where date between date '2009-09-01' and date '2009-09-30') dt
    for instance

  4. #4
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hessodreamy View Post
    what do you mean by tot_NAME? did you want to count the names? or maybe count the distinct names?


    count the names

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swampBoogie View Post
    There are multiple ways of getting that result

    Code:
    select count(*) + (select count(*) 
                         from tbl_a 
                        where date between date '2009-09-01' 
                                       and date '2009-09-30') as tot_NAME,
           sum(av) as tot_AV
      from tbl_b
     where date between date '2009-09-01' 
                    and date '2009-09-30'
    or

    Code:
    select count(*) as tot_NAME, 
           sum(av) as tot_AV
      from (select name,
                   0 as av
              from tbl_a
             where date between date '2009-09-01' and date '2009-09-30'
             union
            select name,
                   av
              from tbl_b
             where date between date '2009-09-01' and date '2009-09-30') dt
    for instance
    Sorry... but your query response with:

    tot_Name = 90
    tot_AV = 1727818762

    If I execute this queries:

    Code:
    SELECT COUNT(*) AS strCOUNT_a FROM tbl_a WHERE date between '2009-09-01' and '2009-09-30'
    
    SELECT COUNT(*) AS strCOUNT_b FROM tbl_b WHERE date between '2009-09-01' and '2009-09-30'
    
    SELECT sum(av) AS strSUM FROM tbl_a WHERE date between '2009-09-01' and '2009-09-30'
    I have:

    strCOUNT_a = 481
    strCOUNT_b = 376
    tot = 857

    strSUM = 9598526

    Why your query tot_NAME = 90 and tot_AV = 1727818762 ?

    thanks x your help.

  6. #6
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Which of my queries did you use?

    I've spotted an error in my second query, it should be union all instead of union.

    In your sum(av) query you have tbl_A while in your example in the first post the AV column is in tbl_b. What should it be?

  7. #7
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I'am sorry... my error in the copy/paste...
    Your query working... GREAT !!!!
    many thanks...


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
  •