CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Reyaz
Reyaz

Reputation: 55

query to count the similar values for each column

Please could you help to get the query for the below table

╔══════════════╗
║ A  B   C   D ║
╠══════════════╣
║ N  E   1  -5 ║
║ N  N   4  -3 ║
║ N  -4  6   E ║
║ 3  E   E  -8 ║
║ 1  N   N   N ║
╚══════════════╝

I need the output as below

╔═════════════════════════════════╗
║    N    E     NegativeValues    ║
╠═════════════════════════════════╣
║ A  3    0     0                 ║
║ B  2    2     1                 ║
║ C  1    1     0                 ║
║ D  1    1     3                 ║
╚═════════════════════════════════╝

i need to get the count for N and E and negative values for all the columns. Please help. Thanks in advance

Update Here N is NULL and E starts with the string "-1E+70"

Upvotes: 3

Views: 194

Answers (4)

Gidil
Gidil

Reputation: 4137

Try un-pivoting and then conditionally counting the rows.
Something like this:

SELECT ABC, 
       Sum(CASE 
             WHEN VALS = 'n' THEN 1 
             ELSE 0 
           END) n, 
       Sum(CASE 
             WHEN VALS = 'e' THEN 1 
             ELSE 0 
           END) E, 
       Sum(CASE 
             WHEN LEFT(VALS, 1) = '-' THEN 1 
             ELSE 0 
           END) NegativeValues 
FROM   TABLE1 
       UNPIVOT (VALS 
               FOR ABC IN ([A], 
                           [B], 
                           [C], 
                           [D]))t 
GROUP  BY ABC

Take a look at a working example on SQL Fiddle.
Good Luck!

Taking into account that the 'n' value is actually NULL and that the UNPIVOT function drops NULL values and that the 'e' value is actually '-1e', here is an updated version of the query:

SELECT ABC, 
       Sum(CASE 
             WHEN VALS = 'NULL' THEN 1 
             ELSE 0 
           END) n, 
       Sum(CASE 
             WHEN VALS = '-1e' THEN 1 
             ELSE 0 
           END) E, 
       Sum(CASE 
             WHEN LEFT(VALS, 1) = '-' 
                  AND Charindex('e', VALS) = 0 THEN 1 
             ELSE 0 
           END) NegativeValues 
FROM   (SELECT Isnull(A, 'NULL')A, 
               Isnull(B, 'NULL')B, 
               Isnull(C, 'NULL')C, 
               Isnull(D, 'NULL')D 
        FROM   TABLE1)T 
       UNPIVOT (VALS 
               FOR ABC IN ([A], 
                           [B], 
                           [C], 
                           [D]))t 
GROUP  BY ABC

And here is an updated link to the SQL Fiddle.

Edit With the new info that -1e are actually scientific numbers and that all the fields are float types, here is my updated solution (hopefully final):

SELECT ABC, 
       Sum(CASE 
             WHEN VALS = 'NULL' THEN 1 
             ELSE 0 
           END) n, 
       Sum(CASE 
             WHEN VALS LIKE '-1e+070%' THEN 1 
             ELSE 0 
           END) E, 
       Sum(CASE 
             WHEN LEFT(VALS, 1) = '-' 
                  AND Charindex('e', VALS) = 0 THEN 1 
             ELSE 0 
           END) NegativeValues 
FROM   (SELECT Isnull(Cast(A AS VARCHAR(10)), 'NULL')A, 
               Isnull(Cast(B AS VARCHAR(10)), 'NULL')B, 
               Isnull(Cast(C AS VARCHAR(10)), 'NULL')C, 
               Isnull(Cast(D AS VARCHAR(10)), 'NULL')D 
        FROM   TABLE1)T 
       UNPIVOT (VALS 
               FOR ABC IN ([A], 
                           [B], 
                           [C], 
                           [D]))t 
GROUP  BY ABC

And of course a new link to SQL Fiddle.

Upvotes: 7

Romesh
Romesh

Reputation: 2274

If you have only four columns then you can try this solution.

Here is SQLFiddel Demo

Below is the query which you can try :

select 'A' as ColumnName,
       (select count(*) 
          from Table1
         where A = 'N') as N,
       (select count(*) 
          from Table1
         where A = 'E') as E,
       (select count(*) 
          from Table1
         where substring(A,1,1) = '-') as Negative
union
select 'B' as ColumnName,
       (select count(*) 
          from Table1
         where B = 'N') as N,
       (select count(*) 
          from Table1
         where B = 'E') as E,
       (select count(*) 
          from Table1
         where substring(B,1,1) = '-') as Negative
union
select 'C' as ColumnName,
       (select count(*) 
          from Table1
         where C = 'N') as N,
       (select count(*) 
          from Table1
         where C = 'E') as E,
       (select count(*) 
          from Table1
         where substring(C,1,1) = '-') as Negative
union
select 'D' as ColumnName,
       (select count(*) 
          from Table1
         where D = 'N') as N,
       (select count(*) 
          from Table1
         where D = 'E') as E,
       (select count(*) 
          from Table1
         where substring(D,1,1) = '-') as Negative

Upvotes: 1

Devart
Devart

Reputation: 122002

Try this one -

DECLARE @temp TABLE (A CHAR(2), B CHAR(2), C CHAR(2), D CHAR(2))

INSERT INTO @temp (A, B, C, D)
VALUES 
    (NULL, 'E'  , '1', '-5'),
    (NULL, NULL  , '4', '-3'),
    (NULL, '-4' , '6', 'E'),
    ('3', 'E'  , 'E', '-8'),
    ('1', NULL  , NULL, NULL)

SELECT 
      Code
    , N = COUNT(CASE WHEN Value = '' THEN 1 END)
    , E = COUNT(CASE WHEN Value LIKE '%[A-Z]%' THEN 1 END)
    , NegativeValues = COUNT(CASE WHEN LEFT(Value, 1) = '-' THEN 1 END)
FROM (
    SELECT 
          A = ISNULL(A, '')
        , B = ISNULL(B, '')
        , C = ISNULL(C, '')
        , D = ISNULL(D, '')
    FROM @temp
) t
UNPIVOT 
(
    Value FOR Code IN (A, B, C, D)
) unpvt
GROUP BY Code

Upvotes: 2

Sunil Tandon
Sunil Tandon

Reputation: 151

For a more generic solution you can refer the following query:

  SELECT "A", 
       Sum(N) N, 
       Sum(E) E, 
       Sum(M) "Negative" 
FROM   (SELECT CASE 
                 WHEN A = "N" THEN Count(A) 
                 ELSE 0 
               END N, 
               CASE 
                 WHEN A = "E" THEN Count(A) 
                 ELSE 0 
               END E, 
               CASE 
                 WHEN A LIKE "%-%" THEN Count(A) 
                 ELSE 0 
               END "M" 
        FROM   #TAB2 
        GROUP  BY A) aa 
UNION 
SELECT "B", 
       Sum(N) N, 
       Sum(E) E, 
       Sum(M) "Negative" 
FROM   (SELECT CASE 
                 WHEN B = "N" THEN Count(B) 
                 ELSE 0 
               END N, 
               CASE 
                 WHEN B = "E" THEN Count(B) 
                 ELSE 0 
               END E, 
               CASE 
                 WHEN B LIKE "%-%" THEN Count(B) 
                 ELSE 0 
               END "M" 
        FROM   #TAB2 
        GROUP  BY B) bb 
UNION 
SELECT "C", 
       Sum(N) N, 
       Sum(E) E, 
       Sum(M) "Negative" 
FROM   (SELECT CASE 
                 WHEN C = "N" THEN Count(A) 
                 ELSE 0 
               END N, 
               CASE 
                 WHEN C = "E" THEN Count(A) 
                 ELSE 0 
               END E, 
               CASE 
                 WHEN C LIKE "%-%" THEN Count(C) 
                 ELSE 0 
               END "M" 
        FROM   #TAB2 
        GROUP  BY C) cc 
UNION 
SELECT "D", 
       Sum(N) N, 
       Sum(E) E, 
       Sum(M) "Negative" 
FROM   (SELECT CASE 
                 WHEN D = "N" THEN Count(D) 
                 ELSE 0 
               END N, 
               CASE 
                 WHEN D = "E" THEN Count(D) 
                 ELSE 0 
               END E, 
               CASE 
                 WHEN D LIKE "%-%" THEN Count(D) 
                 ELSE 0 
               END "M" 
        FROM   #TAB2 
        GROUP  BY D) dd

Upvotes: 1

Related Questions