Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2008
    Posts
    13

    Unanswered: UNION showing zeros when COUNT(*)==0

    Hello,
    Code:
    SELECT count(*), months.m_disp FROM people p, (
    SELECT '01' as m, 'Jan' as m_disp
    UNION
    SELECT '02' as m, 'Feb' as m_disp
    UNION
    SELECT '03' as m, 'Mar' as m_disp
    UNION 
    SELECT '04' as m, 'Apr' as m_disp
    UNION 
    SELECT '05' as m, 'May' as m_disp
    UNION 
    SELECT '06' as m, 'Jun' as m_disp
    UNION 
    SELECT '07' as m, 'Jul' as m_disp
    UNION 
    SELECT '08' as m, 'Aug' as m_disp
    UNION 
    SELECT '09' as m, 'Sep' as m_disp
    UNION 
    SELECT '10' as m, 'Oct' as m_disp
    UNION 
    SELECT '11' as m, 'Nov' as m_disp
    UNION 
    SELECT '12' as m, 'Dec' as m_disp
    ) months
    GROUP BY months.m

    where clause determines which people to select;
    All works well but months where there are no people maching criteria don't appear.

    So now I get:
    Code:
    4,Jan
    19,Mar
    2,Jul
    6,Aug
    7,Oct
    85,Nov
    92,Dec
    and I'm working on getting:
    Code:
    4,Jan
    0,Feb
    19,Mar
    0,Apr
    0,Maj
    0,Jun
    2,Jul
    6,Aug
    0,Sep
    7,Oct
    85,Nov
    92,Dec
    Share ideas, please


    Regards

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're using an (implied) inner join

    use a LEFT OUTER JOIN instead, with months being the left table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2008
    Posts
    13
    Thank you for your reply, r937!

    I've tried LEFT OUTER JOIN (with proper ON clause) already but still nothing for months that have no records

    Haven't had much experience with UNION: seems that rows built with it don't behave like regular DB table?


    Regards

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Tokajac View Post
    I've tried LEFT OUTER JOIN (with proper ON clause) already but still nothing for months that have no records
    i'm sorry, i can't see your query from here, but i bet there's something wrong with it

    Quote Originally Posted by Tokajac View Post
    Haven't had much experience with UNION: seems that rows built with it don't behave like regular DB table?
    sure they do, your months table is fine

    you shoulda used UNION ALL instead of UNION, but that's a minor point that doesn't really affect the results of the union

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2008
    Posts
    13
    Thank you for your reply, r937!

    Below is the query built according to the guidelines, but month still doesn't show up for 0:

    Code:
    SELECT count(*), months.m_disp FROM (
    SELECT '01' as m, 'Jan' as m_disp
    UNION ALL
    SELECT '02' as m, 'Feb' as m_disp
    UNION ALL
    SELECT '03' as m, 'Mar' as m_disp
    UNION ALL 
    SELECT '04' as m, 'Apr' as m_disp
    UNION ALL 
    SELECT '05' as m, 'May' as m_disp
    UNION ALL 
    SELECT '06' as m, 'Jun' as m_disp
    UNION ALL 
    SELECT '07' as m, 'Jul' as m_disp
    UNION ALL 
    SELECT '08' as m, 'Aug' as m_disp
    UNION ALL 
    SELECT '09' as m, 'Sep' as m_disp
    UNION ALL 
    SELECT '10' as m, 'Oct' as m_disp
    UNION ALL 
    SELECT '11' as m, 'Nov' as m_disp
    UNION ALL 
    SELECT '12' as m, 'Dec' as m_disp
    ) months
    LEFT OUTER JOIN people p ON MONTH(p.bith_dt)=months.m
    GROUP BY months.m
    Any further idea?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you'll want to count the number of matches in the right table, not the number of rows in the left table

    change COUNT(*) to COUNT(p.bith_dt) and see what happens

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2008
    Posts
    13
    Thank you for your reply, r937!

    Months still don't appear for 0 people: I've tried with: COUNT(people.every_column_from_people_table)

    Any further idea?

    Regards

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're still doing something wrong, and once again, i can't see your query from here

    the internet isn't ~totally~ magic, you know... if you want me to help you debug your query, you're just gonna have to show it to me

    meanwhile, i will show you this --
    Code:
    CREATE TABLE people
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , name VARCHAR(9)
    , bith_dt DATE
    );
    INSERT INTO people (name,bith_dt) values
     ( 'curly'    , '1903-10-22' )
    ,( 'larry'    , '1902-10-05' )
    ,( 'moe'      , '1897-06-19' )
    ,( 'shemp'    , '1895-03-17' )
    ,( 'joe'      , '1907-08-12' )
    ,( 'curly joe', '1909-07-12' )
    ;
    
    SELECT months.m_disp
         , COUNT(p.bith_dt)
      FROM ( SELECT  1 as m, 'Jan' as m_disp
             UNION ALL
             SELECT  2, 'Feb'
             UNION ALL
             SELECT  3, 'Mar'
             UNION ALL
             SELECT  4, 'Apr'
             UNION ALL
             SELECT  5, 'May'
             UNION ALL
             SELECT  6, 'Jun'
             UNION ALL
             SELECT  7, 'Jul'
             UNION ALL
             SELECT  8, 'Aug'
             UNION ALL
             SELECT  9, 'Sep'
             UNION ALL
             SELECT 10, 'Oct'
             UNION ALL
             SELECT 11, 'Nov'
             UNION ALL
             SELECT 12, 'Dec') AS months
    LEFT OUTER
      JOIN people p
        ON MONTH(p.bith_dt) = months.m
    GROUP
        BY months.m
    
    m_disp   COUNT(p.bith_dt)
    Jan            0
    Feb            0
    Mar            1
    Apr            0
    May            0
    Jun            1
    Jul            1
    Aug            1
    Sep            0
    Oct            2
    Nov            0
    Dec            0
    vwalah, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2008
    Posts
    13
    Thank you for your reply, r937!

    So for above setup, how to get all months when added clause: WHERE DAY(p.birth_dt)<15 ?

    Code:
    SELECT months.m_disp
         , COUNT(p.birth_dt)
      FROM ( SELECT  1 as m, 'Jan' as m_disp
             UNION ALL
             SELECT  2, 'Feb'
             UNION ALL
             SELECT  3, 'Mar'
             UNION ALL
             SELECT  4, 'Apr'
             UNION ALL
             SELECT  5, 'May'
             UNION ALL
             SELECT  6, 'Jun'
             UNION ALL
             SELECT  7, 'Jul'
             UNION ALL
             SELECT  8, 'Aug'
             UNION ALL
             SELECT  9, 'Sep'
             UNION ALL
             SELECT 10, 'Oct'
             UNION ALL
             SELECT 11, 'Nov'
             UNION ALL
             SELECT 12, 'Dec') AS months
    LEFT OUTER
      JOIN people p
        ON MONTH(p.birth_dt) = months.m
    WHERE DAY(p.birth_dt)<15
    GROUP
        BY months.m

    Now there are
    Code:
    Jul,1
    Aug,1
    Oct,1
    and I need

    Code:
    Jan,0
    Feb,0
    Mar,0
    Apr,0
    May,0
    Jun,0
    Jul,1
    Aug,1
    Sep,0
    Oct,1
    Nov,0
    Dec,0

    Regards

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change WHERE to AND
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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