CODEWITHSUNDEEP
mysqlsqldatabase
code-life balance
code-life balance

Reputation: 319

Why my IfNull() function doesn't work?

SELECT
    f.province AS loc,
    IFNULL(COUNT(f.province), 0) AS count
FROM
    project_name_data d
RIGHT JOIN full_province f ON (
    TRIM(
        REPLACE (
            REPLACE (
                SUBSTRING_INDEX(d.location, '|', 1),
                '省',
                ''
            ),
            '壮族',
            ''
        )
    ) = f.province
)
GROUP BY
    loc;

This is my query Sql, full_province is a table which contains all provinces in China, and project_name_data refers to main data. Now I wanna calculate how much records there are in each province, and for provinces which doesn't have any record give them 0. But my Sql only returns the provinces that include records. Are there some mistakes in it?

For an unseemly example:

-- main table
id   location      comment  other columns
1    Los Angeles   aaa        ...
2    New York      bbb        ...
3    Cambridge     ccc        ...
4    Philadelphia  ddd        ...
5    New York      eee        ...
6    Cambridge     fff        ...

--full_province table
id    location
1     Los Angeles
2     New York
3     Cambridge
4     Philadelphia
5     Beijing
6     Tokyo
7     Barcelona
8     Paris
9     Toronto

expect output:

      location      count
1     Los Angeles     1
2     New York        2
3     Cambridge       2
4     Philadelphia    1
5     Beijing         0
6     Tokyo           0
7     Barcelona       0
8     Paris           0
9     Toronto         0

Upvotes: 1

Views: 198

Answers (4)

D-Shih
D-Shih

Reputation: 46249

IFNULL can be removed, because if d.province value be NULL COUNT will not accumulate. will return 0.

SELECT
    f.province AS loc,
    COUNT(d.province) AS count
FROM
    project_name_data d
RIHGT JOIN full_province f ON (
    TRIM(
        REPLACE (
            REPLACE (
                SUBSTRING_INDEX(d.location, '|', 1),
                '省',
                ''
            ),
            '壮族',
            ''
        )
    ) = f.province
)
GROUP BY f.province;

EDIT

I saw you add some sample data.

You can try this query.

SELECT
    f.location AS loc,
    COUNT(d.location) AS CNT
FROM
    project_name_data d
right JOIN full_province f
ON d.location= f.location
GROUP BY f.location
ORDER BY d.id desc

sqlfiddle

[Results]:

|          loc | CNT |
|--------------|-----|
| Philadelphia |   1 |
|    Cambridge |   2 |
|     New York |   2 |
|  Los Angeles |   1 |
|      Beijing |   0 |
|    Barcelona |   0 |
|      Toronto |   0 |
|        Tokyo |   0 |
|        Paris |   0 |

Upvotes: 2

Koen
Koen

Reputation: 734

Replacing:

IFNULL(COUNT(f.province), 0) AS count

With:

sum(case when d.location is not null then 1 else 0 end)

might work better - you're then counting the number of non-null locations from your main table that are linked to your province table, while getting zero if (due to the right join) you have a null value for location.

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17665

I would substitute sum(case when f.province is not null then 1 else 0 end)

Upvotes: 1

starko
starko

Reputation: 1149

Because COUNT it will never return your value NULL.

Upvotes: 1

Related Questions