CODEWITHSUNDEEP
sqlsql-serversql-server-2008
sharan kumar
sharan kumar

Reputation: 49

Normalization required in sql query

I have a table called student data and having 20 columns in that

i have to write condition on each column like this 

SELECT
    registered, Appeared, registered + Appeared AS col3 
FROM
    (SELECT 
     COUNT(REGDNO) AS registered, branch_code,
     SUM(CASE WHEN SUB1_GRADE <> 'Ab' AND SUB1_GRADE IS NOT NULL OR
                   SUB2_GRADE <> 'Ab' AND SUB1_GRADE IS NOT NULL OR
                   SUB2_GRADE <> 'Ab ' AND SUB2_GRADE IS NOT NULL OR
                   SUB3_GRADE <> 'Ab' AND SUB3_GRADE IS NOT NULL OR 
                   SUB4_GRADE <> 'Ab' AND SUB4_GRADE IS NOT NULL OR  
                   SUB5_GRADE <> 'Ab' AND SUB5_GRADE IS NOT NULL OR 
                   SUB6_GRADE <> 'Ab' AND SUB6_GRADE IS NOT NULL OR 
                   SUB7_GRADE <> 'Ab' AND SUB7_GRADE IS NOT NULL .........

is there any easier way kindly help me please

Upvotes: 0

Views: 63

Answers (2)

cloudsafe
cloudsafe

Reputation: 2504

Using unpivot to check if any of the columns are populated and <> 'Ab'. Note that <>'Ab' returns false for Nulls anyway:

create table rr (a char(2), b char(2), c char(2), d char(2), e char(2), f char(2))

insert into rr values (null,'aa',null,null,'ac','ab')

Select sum(iif(u.[cols]<>'AB', 1,0))
from rr s
unpivot
(
  [cols]
  for [vals] in (a, b, c, d, e, f)
) u;

Upvotes: 0

JusuVh
JusuVh

Reputation: 186

You could use unpivot to make all your columns into one grade column, then apply the condition on that one column.

I'm not familiar with the sql-server synthax (and I'm unable to test it right now) but in postgres, for example, it would be like that :

select sum(colvalue) from (
  select
    unnest(array['col1', 'col2', 'col3']) as colname,
    unnest(array[col1, col2, col3]) as colvalue
  from my_table) x
where colvalue <> 'Ab' and colvalue is not null

Upvotes: 1

Related Questions