UNION showing zeros when COUNT(*)==0

Tokajac · #1 (**permalink**) 12-10-11, 06:34

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

r937 · #2 (**permalink**) 12-10-11, 07:03

you're using an (implied) inner join

use a LEFT OUTER JOIN instead, with months being the left table

Tokajac · #3 (**permalink**) 12-10-11, 08:00

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

r937 · #4 (**permalink**) 12-10-11, 09:07

Quote:

Originally Posted by Tokajac

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

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

Tokajac · #5 (**permalink**) 12-10-11, 09:43

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?

r937 · #6 (**permalink**) 12-10-11, 09:50

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

Tokajac · #7 (**permalink**) 12-10-11, 10:17

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

r937 · #8 (**permalink**) 12-10-11, 11:13

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

Tokajac · #9 (**permalink**) 12-12-11, 02:57

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

r937 · #10 (**permalink**) 12-12-11, 04:39

change WHERE to AND

Internet Services

Web Development

Digital Marketing

Consumer Tech