Count number of Records which have x fields completed

[hyarion]

I have a MySQL database with these 3 fields in one of the tables:

Cell_No, Work_No, Home_No

I need to calculate how many records have only 1 of the fields completed.

E.g. Some example Data:

Name Cell_No Work_No Home_No
John 082123123 021482112 [null]
Mary 082442112 [null] [null]
Mike [null] 021343222 [null]
Andrew [null] 012323241 [null]

I need to do a query which will be used for a report on how many records have 1 number completed, how many have 2 numbers completed, etc.

In the above example it would be:
1 number: 3
2 numbers: 1

I was hoping I could do it with the Count() function but I cannot find any examples on using it for this sort of function. I assume it probably can't do it.

Can anyone suggest how I could accomplish this?

[davide++]

Hi all.

I haven't understood your requirement very well.
To get "how many records have only 1 of the fields completed." you should write a query like this

SELECT COUNT(*)
  FROM MyTable
 WHERE (Cell_No IS NOT NULL AND Work_No IS NULL AND Home_No IS NULL)
    OR (Cell_No IS NULL AND Work_No IS NOT NULL AND Home_No IS NULL)
    OR (Cell_No IS NULL AND Work_No IS NULL AND Home_No IS NOT NULL)

But I didn't understand the example; what do you mean with "number 1 completed"?

[hyarion]

Thank you, sorry I havn't explained it quite well enough. Let me try explain more.

There are multiple users who input data into the system. The data comprises of clients details. At the end of a month I need to pull up a report on the users.

The report needs to show how many records that each user inputted contained only 1 phone number, how many records contained only 2 phone numbers and how many records contained 3 phone numbers.


Query wise, maybe something along these lines (This does not work obviously):

SELECT Count(Cell_No IS NOT NULL AND Work_No IS NULL AND Home_No IS NULL) AS OneNumber, 
Count(Cell_No IS NOT NULL AND Work_No IS NOT NULL AND Home_No IS NULL) AS TwoNumbers,
Count(Cell_No IS NOT NULL AND Work_No IS NOT NULL AND Home_No IS NOT NULL) AS ThreeNumbers
FROM MyTable;

[dglienna]

You'd need 9 statements, for two, or three. Add another column for tot and count them.

SELECT COUNT(*)
  FROM MyTable
 WHERE (Cell_No IS NOT NULL AND Work_No IS NULL AND Home_No IS NULL)
    OR (Cell_No IS NULL AND Work_No IS NOT NULL AND Home_No IS NULL)
    OR (Cell_No IS NULL AND Work_No IS NULL AND Home_No IS NOT NULL)

[KrisSimonis]

Actually, you can do better, you should define a view which checks wether or not 1 or more fields are empty, counts this per record and has a column with the number in it. then counting is a simple matter of selecting a count from the view.

CREATE VIEW ColumnsFilledCounterView
AS
SELECT UniqueID
       , Cell_No
       , Work_No
       , Home_No
       , Counter = CASE WHEN Cell_No IS NULL THEN 1 ELSE 0 END
                 + CASE WHEN Work_No IS NULL THEN 1 ELSE 0 END
                 + CASE WHEN Home_No IS NULL THEN 1 ELSE 0 END
FROM Table

GO

Now it's a simple matter to count the proper number of fields that have been entered like this:

SELECT COUNT(*)
FROM ColumnsFilledCounterView
WHERE Counter = 1

or 2, or 3, of anything you'd like.
Technicly you can stuff the view and the select together into 1 select statement, but this is neater.

[davide++]

OK, maybe I have understood
You need the UNION statement. Look at this query that I've tried on my database:

SELECT 'ONE',
       COUNT(*)
  FROM tipi_cpe
 WHERE (TIPO_CPE_SIEBEL IS NULL AND TIPO_CPE_ADSL IS NOT NULL)
    OR (TIPO_CPE_SIEBEL IS NOT NULL AND TIPO_CPE_ADSL IS NULL)
    AND (TIPO_CPE_SIEBEL IS NOT NULL AND TIPO_CPE_ADSL IS NOT NULL)
UNION
SELECT 'TWO',
       COUNT(*)
  FROM tipi_cpe
 WHERE (TIPO_CPE_SIEBEL IS NOT NULL AND TIPO_CPE_ADSL IS NOT NULL)
    OR (TIPO_CPE_SIEBEL IS NOT NULL AND TIPO_CPE_ADSL IS NOT NULL)

For semplicity I've consider only two field; so the first query counts the records where OR TIPO_CPE_SIEBEL, OR TIPO_CPE_ADSL are filled (but not both), and the second query counts the records where both are filled.

I hope this will help you.