MySQL Query for finding the Record [closed]

Question

I have a table and the structure is like

+-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| ContactID             | varchar(50)  | YES  |     | NULL    |       |
| ContactFirstName      | varchar(50)  | YES  |     | NULL    |       |
| ContactLastName       | varchar(50)  | YES  |     | NULL    |       |
| ContactEmailID        | varchar(50)  | YES  |     | NULL    |       |
| AccountCharterNo      | varchar(50)  | YES  | MUL | NULL    |       |
| AccountName           | varchar(100) | YES  |     | NULL    |       |
| AccountID             | varchar(50)  | YES  | MUL | NULL    |       |
| ActiveInactiveAccount | varchar(50)  | YES  |     | NULL    |       |
| id                    | varchar(50)  | YES  |     | NULL    |       |
| name                  | varchar(50)  | YES  |     | NULL    |       |
| acc_charter_number_c  | varchar(50)  | YES  | MUL | NULL    |       |
| account_type          | varchar(50)  | YES  |     | NULL    |       |
| person_type           | varchar(50)  | YES  |     | NULL    |       |
+-----------------------+--------------+------+-----+---------+-------+

I Need to find distinct AccountID where acc_charter_number_c is blank.

The AccountID is duplicate and it might possible that in one record acc_charter_number_c is given but at another place that is blank for same AccountID. i need to filter these AccountID from the output.

Answer 1

try this:

SELECT AccountID,
       SUM(IF(acc_charter_number_c <> '', 1, 0) AS valid_cnt
       SUM(IF(acc_charter_number_c = '', 1, 0) AS blank_cnt
FROM table_name
GROUP BY AccountID
HAVING (valid_cnt> 1 AND blank_cnt > 1);

Answer 2

This one?

select 
  AccountID, 
  SUM( IF(acc_charter_number_c IS NULL || acc_charter_number_c = '', 0, 1) ) as novalues 
from 
  testTable 
GROUP BY AccountID 
HAVING novalues = 0;

I have tried on a sample set like this:

+-----------+----------------------+
| AccountID | acc_charter_number_c |
+-----------+----------------------+
| 1         | NULL                 |
| 1         | 1                    |
| 2         | 1                    |
| 2         | 1                    |
| 2         | 1                    |
| 3         | 1                    |
| 4         | NULL                 |
| 4         | 1                    |
| 5         | NULL                 |
| 6         | NULL                 |
| 6         | NULL                 |
| 6         | 1                    |
+-----------+----------------------+

And here the output:

+-----------+----------+
| AccountID | novalues |
+-----------+----------+
| 5         |        0 |
+-----------+----------+