how to count from single row RSS

• • Reply
  

  yatinpatel13...

  Member

  39 Points

  57 Posts

  how to count from single row

  Jan 10, 2012 10:54 AM|LINK

  i have one table: columns like

  table name: empattd

  supervisorname,designation,ds_lname,d1,d2,d3,d4,.....d31

  i have retuen query like

  select supervisorname,d1,d2,d3,d4,.....d31 from empattd

  RAHUL ROY P P P P....P

  I want also count no of total P for RAHUL ROY

  Yatin patel
• • Reply
  

  jeeveshfulor...

  Participant

  1562 Points

  287 Posts

  Re: how to count from single row

  Jan 10, 2012 11:40 AM|LINK

  Try to do like this:-

  create table Empattd(supervisorname varchar(10),designation varchar(10),ds_lname varchar(10),
  d1 varchar(10),d2 varchar(10),d3 varchar(10),d4 varchar(10),
  d5 varchar(10),d6 varchar(10),d7 varchar(10),d8 varchar(10),
  d9 varchar(10),d10 varchar(10),d11 varchar(10),d12 varchar(10))
  insert into Empattd values('rahul',null,null,'p','p','p','p','p','p','p','p','p','p','p','p')
  ,('ramesh',null,null,'p','p',null,'p','p','p',null,'p','p','p',null,'p')
  ,('sohan',null,null,'p',null,'p','p','p',null,'p','p','p','p','p',null)
  ,('raj',null,null,'p','p',null,'p','p','p',null,'p','p','p',null,'p')
  
  select * from Empattd
  
  select J1.supervisorname,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10,d11,d12,j2.CountOfP from Empattd J1
  inner join
  (    select supervisorname,
  			CountOfP=(d1+d2+d3+d4+d5+d6+d7+d8+d9+d10+d11+d12)
  	 from (
  			select supervisorname,
  			d1=case when d1='p' then 1 else 0 end  ,
  			d2=case when d2='p' then 1 else 0 end  ,
  			d3=case when d3='p' then 1  else 0 end  ,
  			d4=case when d4='p' then 1  else 0 end  ,
  			d5=case when d5='p' then 1  else 0 end  ,
  			d6=case when d6='p' then 1 else 0 end  ,
  			d7=case when d7='p' then 1  else 0 end  ,
  			d8=case when d8='p' then 1  else 0 end  ,
  			d9=case when d9='p' then 1  else 0 end  ,
  			d10=case when d10='p' then 1  else 0 end  ,
  			d11=case when d11='p' then 1  else 0 end  ,
  			d12=case when d12='p' then 1 else 0 End
  			 from Empattd
  		  )A
   )J2 on J1.supervisorname=j2.supervisorname

• • Reply
  

  Dan Bracuk

  Contributor

  3970 Points

  1096 Posts

  Re: how to count from single row

  Jan 10, 2012 12:08 PM|LINK

  Normalize your database and things like this will be simple.

• • Reply
  

  limno

  All-Star

  117340 Points

  8005 Posts

  Moderator

  MVP

  Re: how to count from single row

  Jan 10, 2012 01:16 PM|LINK

  You can use UNPIVOT to normalize your table and PIVOT the result. A better way is to learn how to normalize your table in your design phase.

  Here is a sample query for your question:

  WITH mycte
       AS (SELECT supervisorname,val,col,Count(*) OVER(Partition BY supervisorname) AS cnt
           FROM   (SELECT supervisorname,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10,d11,d12
                   FROM   Empattd) src UNPIVOT (val FOR Col IN (d1, d2, d3, d4, d5, d6, d7, d8, d9, d10, d11, d12)) unpvt)
  SELECT *
  FROM   (SELECT *
          FROM   mycte) src2 PIVOT(Max(val) FOR col IN (d1, d2, d3, d4, d5, d6, d7, d8, d9, d10, d11, d12)) pvt

  Limno
  
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
  • Marked as answer by Chen Yu - MSFT on Jan 16, 2012 06:33 AM
• • Reply
  

  shivv

  Participant

  1566 Points

  283 Posts

  Re: how to count from single row

  Jan 10, 2012 03:12 PM|LINK

  Here is the solution:

  SELECT * FROM Empattd Inner join ( SELECT Supervisorname, COUNT(*) countAttandance FROM (SELECT Supervisorname, val
   FROM Empattd  UNPIVOT ( val FOR [Col] In (d1, d2,d3,d4,d5,d6,d7,d8,d9,d10,d11,d12)) as Attendance) Attendance WHERE val = 'p' GROUP BY Supervisorname
   
   ) as CountAtt ON Empattd.Supervisorname = CountAtt.Supervisorname