Dealing with aggregate functions using DB_DataObject

PHP
1

Hi all

here is my problem :

I have 3 tables named “supergroup”, “month” and “child” as the following, that I need to join them :



[B]supergroup[/B] structure  : id, name
[B]report_month[/B] structure : id, year, month, supergroup
[B]report_child[/B] structure : id, month_id, info (contains four rows via each month_id

LIKE this :

[B]supergroup[/B]

ID  NAME
1      s1  
2      s2
3      s3
4      s4
5      s5
6      s6
7      s7
[COLOR="Blue"]
[I]note : supergroups are unique[/I][/COLOR]

[B]report_month[/B]

ID    YEAR    MONTH      SUPERGROUP
1      2011        7                     s1
2      2011        8                     s1
3      2011        9                     s1
4      2011       10                    s1
5      2011       10                    s2

[B]report_child[/B]

ID  MONTH_ID  INFO
1        1            info_1_1
2        1            info_1_2
3        1            info_1_3
4        1            info_1_4
5        2            info_2_1
6        2            info_2_2
7        2            info_2_3
8        2            info_2_4
9        5            info_5_1
10      5            info_5_2
11      5            info_5_3
12      5            info_5_4

Here's is the sql code I implement with DB_DataObject :

SELECT supergroup.id AS supergroup_id, supergroup.name AS supergroup_name, COUNT( DISTINCT IF( year = 2011 AND month >= 1 AND month <= 12
AND report_child.month_id IS NOT NULL , 1, 0 ) ) AS month_count
FROM supergroup
LEFT JOIN report_month ON supergroup.name = report_month.supergroup
LEFT JOIN report_child ON report_month.id = report_child.month_id
GROUP BY supergroup.id

The sql query result is as follows :


ID  NAME   MONTH_COUNT
1      s1      2    [I]=> correct[/I]
2      s2      1    [I]=> correct[/I]
3      s3      1    [I]=> wrong I should get 0[/I]
4      s4      1    [I]=> wrong I should get 0[/I]
5      s5      1    [I]=> wrong I should get 0[/I]
6      s6      1    [I]=> wrong I should get 0[/I]
7      s7      1    [I]=> wrong I should get 0[/I]

I don’t get where is the problem , what is wrong with my count function ?

Thanks in advance for your help.

2

your COUNT expression is set up to count a bunch of 1s and 0s

since 0 is not null, it counts those the same way it counts the 1s

it would work better if you used SUM instead of COUNT

3

Thanks r397 for your help , now I get what was going on , I changed the COUNT part of my query to this :



COUNT( DISTINCT IF( year = 2011 AND month >= 1 AND month <= 12
AND report_child.month_id IS NOT NULL, report_child.month_id, NULL ) ) AS month_count

now it works beautifully :slight_smile:

Thanks again