Use of COUNT(DISTINCT myfield)

Question

I am puzzled by a SELECT query I am trying to make. Basically my file has one or more records per invoice, showing its region. I want to find how many invoices each region has.

Here is an example of my table Table1

InvNo   Region
A1      1
A2      3
A3      2
A4      1
A5      1
A6      2

Running the following SQL code gives me what I want BUT includes one extra record I am not expecting - and do not want, apparently for a blank region. I cannot see why this row arises.

SELECT Region, COUNT(DISTINCT Invno) as count
    FROM table1
    GROUP BY Region

Here is the output

Region  count
        0       ' I do not want this row!
1       3
2       2
3       1

Should I rephrase the code? Or could this be a problem with the SQL platform I am using (VistaDB)?

UPDATE The code works fine if I do not call my new column count but myCount i.e. this works COUNT(DISTINCT Invno) as myCount. This reminds me that one should always be careful about the name one gives to variables etc.

Answer 1

Try this

SELECT Region, COUNT(DISTINCT Invno) as count
FROM table1
GROUP BY Region
HAVING COUNT(DISTINCT Invno) > 0

Answer 2

You have a row where region and inv_no are both NULL (at least inv_no is). You can just filter it out:

SELECT Region, COUNT(DISTINCT Invno) as count
FROM table1
WHERE Invno is not null
GROUP BY Region;

Answer 3

It works fine for me.

I tried it with InvNo and null region, and null InvNo, both null, and even with a region = 0. The count never returns zero, so I'd say it is a feature with vistadb. But I tested on MySQL, not vistadb.