How to get count for each combination of col1 & col2 on specific conditions for col3

1
votes

Table: I have a database table table_name2 in SQL Server 2012 with data as:

col1    col2    col3
one     first   abc-3ab9-cd-f-01
two     second  abcdefg04
three   first   
three   second  mnopqrs03
one     first   NULL
two     first   'NULL' 
one     second  papapap
two     second  dbrf434
two     first   434jhpo

DDL: And created it using

CREATE TABLE [dbo].[table_name2](
    [col1] [nvarchar](255) NULL,
    [col2] [nvarchar](255) NULL,
    [col3] [nvarchar](255) NULL
) ON [PRIMARY];

Data populated:

insert into table_name2
values ('one','first','abc-3ab9-cd-f-01'),
('two','second','abcdefg04'),
('three','first',''),
('three','second','mnopqrs03'),
('one','first',NULL),
('two','first','NULL'),
('one','second','papapap'),
('two','second','dbrf434'),
('two','first','434jhpo');

I want to list each combination of col1 and col2. Then count of col3 for each of this col1/col2 combination where col3 is either blank, 'N/A', NULL or 'NULL'.

For those combinations of col1/col2 where none of the conditions related to col3 are met, I want to show 0 for those combinations.

My desired result is:

col1    col2    MainCount
one     first   1
one     second  0
three   first   1
three   second  0
two     first   1
two     second  0

What I have tried: For now I have tried a simple query and two modified versions of it

SELECT col1, col2, count(col3) as MainCount
FROM table_name2
WHERE col3 is null OR col3 = '' OR col3 = 'N/A' OR col3 = 'Unknown' OR col3 = 'NULL'
GROUP BY col1, col2
ORDER BY col1, col2;

Gives:

col1    col2    MainCount
one     first   0
three   first   1
two     first   1

Then

SELECT col1, col2, count(col3) as MainCount
FROM table_name2
WHERE col3 =
    CASE WHEN
        (col3 is null OR col3 = '' OR col3 = 'N/A' OR col3 = 'Unknown' OR col3 = 'NULL')
         THEN col3 ELSE NULL
    END
GROUP BY col1, col2
ORDER BY col1, col2;

Gives:

col1    col2    MainCount
three   first   1
two     first   1

And last one

SELECT col1, col2, count(CASE WHEN col3 is not null THEN col3 ELSE 0 END) as MainCount  
FROM table_name2
WHERE col3 is null OR col3 = '' OR col3 = 'N/A' OR col3 = 'Unknown' OR col3 = 'NULL'
GROUP BY col1, col2
ORDER BY col1, col2;

Gives error as:

Conversion failed when converting the nvarchar value 'NULL' to data type int.

What am I doing wrong here and how can I get the desired output?

4
sqlsql-server-2012

4 Answers

3
votes

Include those conditions in count.

SELECT col1, col2,
count(case when col3 is null OR col3 = '' OR col3 = 'N/A' OR col3 = 'Unknown' OR col3 = 'NULL' then 1 end) as MainCount
FROM table_name2
GROUP BY col1, col2
ORDER BY col1, col2;
1
votes
select col1, col2
  , sum(case 
      when col3 is null then 1 
      when col3 in('','N/A','Unknown','NULL') then 1
      else 0
      end) as MainCount
from table_name2
group by col1, col2
order by col1, col2

rextester demo: http://rextester.com/QBOJ61516

returns:

+-------+--------+-----------+
| col1  |  col2  | MainCount |
+-------+--------+-----------+
| one   | first  |         1 |
| one   | second |         0 |
| three | first  |         1 |
| three | second |         0 |
| two   | first  |         1 |
| two   | second |         0 |
+-------+--------+-----------+
1
votes

It's because col3 doesn't contain a number but you're using a 0 in the case statement. Try this:

SELECT col1
,col2
,sum(CASE WHEN col3 is null THEN 1
          WHEN col3 in ('', 'N/A', 'Unknown', 'NULL') THEN 1
          ELSE 0 END) as MainCount  
FROM table_name2
GROUP BY col1, col2
ORDER BY col1, col2;
1
votes

Try this out and let me know in case of any queries. 

select col1,col2,sum(case when col3 IS NULL OR COL3 in ('NULL', '', 'N/A') THEN 1 ELSE 0 END) AS MAINCOUNT from table_name2
group by col1,col2
ORDER BY COL1,COL2;