Loading ...
Sorry, an error occurred while loading the content.

Re: [SQLQueriesNoCode] Count NON NULL values

Expand Messages
  • Noman Aftab
    Actually I cannot do it like this...The reason is that I am also counting on other columns and I also have a group by clause. Here is the complete query:
    Message 1 of 7 , May 8, 2007
    • 0 Attachment
      Actually I cannot do it like this...The reason is that I am also counting on other columns and I also have a group by clause.
      Here is the complete query:

      Column Type
      CODE varchar
      ISSUED datetime
      USED datetime (Nullable)
      WaitTime int
      ServeTime int


      SELECT CODE,

      SUM(CASE ISSUED WHEN NULL THEN 0 ELSE 1 END) AS [Total Issued],

      SUM(CASE USED WHEN NULL THEN 0 ELSE 1 END) AS [Total Used],

      SUM(WaitTime) AS [Total Wait Time],

      SUM(ServeTime) AS [Total Serve Time]

      FROM Table1

      GROUP BY CODE



      Regards,
      Noman Mohammad Aftab,
      Software Engineer,
      PrisLogix.


      ----- Original Message ----
      From: Saravana Gandhi K <saravana_gandhi@...>
      To: SQLQueriesNoCode@yahoogroups.com
      Sent: Monday, 7 May, 2007 11:26:00 PM
      Subject: RE: [SQLQueriesNoCode] Count NON NULL values













      Hi use the below query



      select count(Date_Column) from tblName where Date_Column is NOT NULL



      Thanks,



      Saravana



      _____



      From: SQLQueriesNoCode@ yahoogroups. com

      [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Noman Aftab

      Sent: Monday, May 07, 2007 11:20 PM

      To: SQLQueriesNoCode@ yahoogroups. com

      Subject: [SQLQueriesNoCode] Count NON NULL values



      hi all,

      I am trying to count the non-null values in a datetime column in SQL

      Server...The query is



      select SUM( CASE IssueDate WHEN NULL THEN 0 ELSE 1 END)

      from Table1



      Instead of returning the count of non-null values, it is returning the total

      number of records.



      What I am missing?



      Regards,

      Noman Mohammad Aftab,

      Software Engineer,

      PrisLogix.



      ----- Original Message ----

      From: Surya Saputra <surya.saputra@ <mailto:surya. saputra%40profes cipta.com>

      profescipta. com>

      To: SQLQueriesNoCode@ <mailto:SQLQueriesN oCode%40yahoogro ups.com>

      yahoogroups. com

      Sent: Monday, 7 May, 2007 9:17:35 AM

      Subject: RE: [SQLQueriesNoCode] Select in string



      Thanks all



      _____



      From: Michael Weiss [mailto:mweiss@ x5solutions. com]



      Sent: Saturday, May 05, 2007 2:57 AM



      To: SQLQueriesNoCode@ yahoogroups. com



      Subject: RE: [SQLQueriesNoCode] Select in string



      One way to do this is to use dynamic sql. Make your entire sql statement



      a string and then execute it.



      DECLARE @sqlString varchar(4000)



      DECLARE @Str varchar(1000)



      Set @Str = '''Jakarta'' ,''Bandung' ''



      SET @sqlString = 'Select City From COM_SUPPLIER Where City In ('+ @Str



      +')'



      Exec @sqlString



      <http://geo.yahoo com/serv? s=97359714/ grpId=9438758/ grpspId=17051153

      66/m



      sgId=2865/stime= 1178308902/ nc1=3848576/ nc2=3848547/ nc3=3848642>



      [Non-text portions of this message have been removed]



      <!--



      #ygrp-mlmsg {font-size:13px; font-family: arial, helvetica, clean,

      sans-serif;}

      #ygrp-mlmsg table {font-size:inherit; font:100% ;}

      #ygrp-mlmsg select, input, textarea {font:99% arial, helvetica, clean,

      sans-serif;}

      #ygrp-mlmsg pre, code {font:115% monospace;}

      #ygrp-mlmsg * {line-height: 1.22em;}

      #ygrp-text{

      font-family: Georgia;

      }

      #ygrp-text p{

      margin:0 0 1em 0;}

      #ygrp-tpmsgs{

      font-family: Arial;

      clear:both;}

      #ygrp-vitnav{

      padding-top: 10px;font- family:Verdana; font-size: 77%;margin: 0;}

      #ygrp-vitnav a{

      padding:0 1px;}

      #ygrp-actbar{

      clear:both;margin: 25px 0;white-space: nowrap;color: #666;text- align:right; }

      #ygrp-actbar .left{

      float:left;white- space:nowrap; }

      .bld{font-weight: bold;}

      #ygrp-grft{

      font-family: Verdana;font- size:77%; padding:15px 0;}

      #ygrp-ft{

      font-family: verdana;font- size:77%; border-top: 1px solid #666;

      padding:5px 0;

      }

      #ygrp-mlmsg #logo{

      padding-bottom: 10px;}



      #ygrp-vital{

      background-color: #e0ecee;margin- bottom:20px; padding:2px 0 8px 8px;}

      #ygrp-vital #vithd{

      font-size:77% ;font-family: Verdana;font- weight:bold; color:#333; text-transform

      :uppercase;}

      #ygrp-vital ul{

      padding:0;margin: 2px 0;}

      #ygrp-vital ul li{

      list-style-type: none;clear: both;border: 1px solid #e0ecee;

      }

      #ygrp-vital ul li .ct{

      font-weight: bold;color: #ff7900;float: right;width: 2em;text- align:right; paddin

      g-right:.5em; }

      #ygrp-vital ul li .cat{

      font-weight: bold;}

      #ygrp-vital a {

      text-decoration: none;}



      #ygrp-vital a:hover{

      text-decoration: underline; }



      #ygrp-sponsor #hd{

      color:#999;font- size:77%; }

      #ygrp-sponsor #ov{

      padding:6px 13px;background- color:#e0ecee; margin-bottom: 20px;}

      #ygrp-sponsor #ov ul{

      padding:0 0 0 8px;margin:0; }

      #ygrp-sponsor #ov li{

      list-style-type: square;padding: 6px 0;font-size: 77%;}

      #ygrp-sponsor #ov li a{

      text-decoration: none;font- size:130% ;}

      #ygrp-sponsor #nc {

      background-color: #eee;margin- bottom:20px; padding:0 8px;}

      #ygrp-sponsor .ad{

      padding:8px 0;}

      #ygrp-sponsor .ad #hd1{

      font-family: Arial;font- weight:bold; color:#628c2a; font-size: 100%;line- height:

      122%;}

      #ygrp-sponsor .ad a{

      text-decoration: none;}

      #ygrp-sponsor .ad a:hover{

      text-decoration: underline; }

      #ygrp-sponsor .ad p{

      margin:0;}

      o {font-size:0; }

      .MsoNormal {

      margin:0 0 0 0;}

      #ygrp-text tt{

      font-size:120% ;}

      blockquote{margin: 0 0 0 4px;}

      .replbq {margin:4;}

      -->



      ____________ _________ _________ _________ _________ _________ _

      Yahoo! Answers - Got a question? Someone out there knows the answer. Try it

      now.

      http://uk.answers <http://uk.answers yahoo.com/> yahoo.com/



      [Non-text portions of this message have been removed]



      ********** DISCLAIMER **********

      Information contained and transmitted by this E-MAIL is proprietary to

      Sify Limited and is intended for use only by the individual or entity to

      which it is addressed, and may contain information that is privileged,

      confidential or exempt from disclosure under applicable law. If this is a

      forwarded message, the content of this E-MAIL may not have been sent with

      the authority of the Company. If you are not the intended recipient, an

      agent of the intended recipient or a person responsible for delivering the

      information to the named recipient, you are notified that any use,

      distribution, transmission, printing, copying or dissemination of this

      information in any way or in any manner is strictly prohibited. If you have

      received this communication in error, please delete this mail & notify us

      immediately at admin@sifycorp. com



      [Non-text portions of this message have been removed]














      <!--

      #ygrp-mlmsg {font-size:13px;font-family:arial, helvetica, clean, sans-serif;}
      #ygrp-mlmsg table {font-size:inherit;font:100%;}
      #ygrp-mlmsg select, input, textarea {font:99% arial, helvetica, clean, sans-serif;}
      #ygrp-mlmsg pre, code {font:115% monospace;}
      #ygrp-mlmsg * {line-height:1.22em;}
      #ygrp-text{
      font-family:Georgia;
      }
      #ygrp-text p{
      margin:0 0 1em 0;}
      #ygrp-tpmsgs{
      font-family:Arial;
      clear:both;}
      #ygrp-vitnav{
      padding-top:10px;font-family:Verdana;font-size:77%;margin:0;}
      #ygrp-vitnav a{
      padding:0 1px;}
      #ygrp-actbar{
      clear:both;margin:25px 0;white-space:nowrap;color:#666;text-align:right;}
      #ygrp-actbar .left{
      float:left;white-space:nowrap;}
      .bld{font-weight:bold;}
      #ygrp-grft{
      font-family:Verdana;font-size:77%;padding:15px 0;}
      #ygrp-ft{
      font-family:verdana;font-size:77%;border-top:1px solid #666;
      padding:5px 0;
      }
      #ygrp-mlmsg #logo{
      padding-bottom:10px;}

      #ygrp-vital{
      background-color:#e0ecee;margin-bottom:20px;padding:2px 0 8px 8px;}
      #ygrp-vital #vithd{
      font-size:77%;font-family:Verdana;font-weight:bold;color:#333;text-transform:uppercase;}
      #ygrp-vital ul{
      padding:0;margin:2px 0;}
      #ygrp-vital ul li{
      list-style-type:none;clear:both;border:1px solid #e0ecee;
      }
      #ygrp-vital ul li .ct{
      font-weight:bold;color:#ff7900;float:right;width:2em;text-align:right;padding-right:.5em;}
      #ygrp-vital ul li .cat{
      font-weight:bold;}
      #ygrp-vital a {
      text-decoration:none;}

      #ygrp-vital a:hover{
      text-decoration:underline;}

      #ygrp-sponsor #hd{
      color:#999;font-size:77%;}
      #ygrp-sponsor #ov{
      padding:6px 13px;background-color:#e0ecee;margin-bottom:20px;}
      #ygrp-sponsor #ov ul{
      padding:0 0 0 8px;margin:0;}
      #ygrp-sponsor #ov li{
      list-style-type:square;padding:6px 0;font-size:77%;}
      #ygrp-sponsor #ov li a{
      text-decoration:none;font-size:130%;}
      #ygrp-sponsor #nc {
      background-color:#eee;margin-bottom:20px;padding:0 8px;}
      #ygrp-sponsor .ad{
      padding:8px 0;}
      #ygrp-sponsor .ad #hd1{
      font-family:Arial;font-weight:bold;color:#628c2a;font-size:100%;line-height:122%;}
      #ygrp-sponsor .ad a{
      text-decoration:none;}
      #ygrp-sponsor .ad a:hover{
      text-decoration:underline;}
      #ygrp-sponsor .ad p{
      margin:0;}
      o {font-size:0;}
      .MsoNormal {
      margin:0 0 0 0;}
      #ygrp-text tt{
      font-size:120%;}
      blockquote{margin:0 0 0 4px;}
      .replbq {margin:4;}
      -->








      ___________________________________________________________
      Yahoo! Answers - Got a question? Someone out there knows the answer. Try it
      now.
      http://uk.answers.yahoo.com/

      [Non-text portions of this message have been removed]
    • rahulk1000
      PLease use select count(condition) instead of total, as total will return total :-) ... SQL Server...The query is ... the total number of records. ...
      Message 2 of 7 , May 8, 2007
      • 0 Attachment
        PLease use select count(condition) instead of total, as total will
        return total :-)



        --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab <noman17pk@...>
        wrote:
        >
        > hi all,
        > I am trying to count the non-null values in a datetime column in
        SQL Server...The query is
        >
        > select SUM( CASE IssueDate WHEN NULL THEN 0 ELSE 1 END)
        > from Table1
        >
        > Instead of returning the count of non-null values, it is returning
        the total number of records.
        >
        > What I am missing?
        >
        > Regards,
        > Noman Mohammad Aftab,
        > Software Engineer,
        > PrisLogix.
        >
        >
        > ----- Original Message ----
        > From: Surya Saputra <surya.saputra@...>
        > To: SQLQueriesNoCode@yahoogroups.com
        > Sent: Monday, 7 May, 2007 9:17:35 AM
        > Subject: RE: [SQLQueriesNoCode] Select in string
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        > Thanks all
        >
        >
        >
        > _____
        >
        >
        >
        > From: Michael Weiss [mailto:mweiss@x5solutions. com]
        >
        > Sent: Saturday, May 05, 2007 2:57 AM
        >
        > To: SQLQueriesNoCode@ yahoogroups. com
        >
        > Subject: RE: [SQLQueriesNoCode] Select in string
        >
        >
        >
        > One way to do this is to use dynamic sql. Make your entire sql
        statement
        >
        > a string and then execute it.
        >
        >
        >
        > DECLARE @sqlString varchar(4000)
        >
        >
        >
        > DECLARE @Str varchar(1000)
        >
        >
        >
        > Set @Str = '''Jakarta'' ,''Bandung' ''
        >
        > SET @sqlString = 'Select City From COM_SUPPLIER Where City In ('+
        @Str
        >
        > +')'
        >
        >
        >
        > Exec @sqlString
        >
        >
        >
        > <http://geo.yahoo com/serv? s=97359714/ grpId=9438758/
        grpspId=17051153 66/m
        >
        > sgId=2865/stime= 1178308902/ nc1=3848576/ nc2=3848547/ nc3=3848642>
        >
        >
        >
        >
        >
        > [Non-text portions of this message have been removed]
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        > <!--
        >
        > #ygrp-mlmsg {font-size:13px;font-family:arial, helvetica, clean,
        sans-serif;}
        > #ygrp-mlmsg table {font-size:inherit;font:100%;}
        > #ygrp-mlmsg select, input, textarea {font:99% arial, helvetica,
        clean, sans-serif;}
        > #ygrp-mlmsg pre, code {font:115% monospace;}
        > #ygrp-mlmsg * {line-height:1.22em;}
        > #ygrp-text{
        > font-family:Georgia;
        > }
        > #ygrp-text p{
        > margin:0 0 1em 0;}
        > #ygrp-tpmsgs{
        > font-family:Arial;
        > clear:both;}
        > #ygrp-vitnav{
        > padding-top:10px;font-family:Verdana;font-size:77%;margin:0;}
        > #ygrp-vitnav a{
        > padding:0 1px;}
        > #ygrp-actbar{
        > clear:both;margin:25px 0;white-space:nowrap;color:#666;text-
        align:right;}
        > #ygrp-actbar .left{
        > float:left;white-space:nowrap;}
        > .bld{font-weight:bold;}
        > #ygrp-grft{
        > font-family:Verdana;font-size:77%;padding:15px 0;}
        > #ygrp-ft{
        > font-family:verdana;font-size:77%;border-top:1px solid #666;
        > padding:5px 0;
        > }
        > #ygrp-mlmsg #logo{
        > padding-bottom:10px;}
        >
        > #ygrp-vital{
        > background-color:#e0ecee;margin-bottom:20px;padding:2px 0 8px 8px;}
        > #ygrp-vital #vithd{
        > font-size:77%;font-family:Verdana;font-weight:bold;color:#333;text-
        transform:uppercase;}
        > #ygrp-vital ul{
        > padding:0;margin:2px 0;}
        > #ygrp-vital ul li{
        > list-style-type:none;clear:both;border:1px solid #e0ecee;
        > }
        > #ygrp-vital ul li .ct{
        > font-weight:bold;color:#ff7900;float:right;width:2em;text-
        align:right;padding-right:.5em;}
        > #ygrp-vital ul li .cat{
        > font-weight:bold;}
        > #ygrp-vital a {
        > text-decoration:none;}
        >
        > #ygrp-vital a:hover{
        > text-decoration:underline;}
        >
        > #ygrp-sponsor #hd{
        > color:#999;font-size:77%;}
        > #ygrp-sponsor #ov{
        > padding:6px 13px;background-color:#e0ecee;margin-bottom:20px;}
        > #ygrp-sponsor #ov ul{
        > padding:0 0 0 8px;margin:0;}
        > #ygrp-sponsor #ov li{
        > list-style-type:square;padding:6px 0;font-size:77%;}
        > #ygrp-sponsor #ov li a{
        > text-decoration:none;font-size:130%;}
        > #ygrp-sponsor #nc {
        > background-color:#eee;margin-bottom:20px;padding:0 8px;}
        > #ygrp-sponsor .ad{
        > padding:8px 0;}
        > #ygrp-sponsor .ad #hd1{
        > font-family:Arial;font-weight:bold;color:#628c2a;font-
        size:100%;line-height:122%;}
        > #ygrp-sponsor .ad a{
        > text-decoration:none;}
        > #ygrp-sponsor .ad a:hover{
        > text-decoration:underline;}
        > #ygrp-sponsor .ad p{
        > margin:0;}
        > o {font-size:0;}
        > .MsoNormal {
        > margin:0 0 0 0;}
        > #ygrp-text tt{
        > font-size:120%;}
        > blockquote{margin:0 0 0 4px;}
        > .replbq {margin:4;}
        > -->
        >
        >
        >
        >
        >
        >
        >
        >
        > ___________________________________________________________
        > Yahoo! Answers - Got a question? Someone out there knows the
        answer. Try it
        > now.
        > http://uk.answers.yahoo.com/
        >
        > [Non-text portions of this message have been removed]
        >
      • Arnie Rowland
        Count() does NOT count NULL values in the column counted (it will however, count any non-NULL value -empty strings, blanks, etc.) Either of these statements
        Message 3 of 7 , May 8, 2007
        • 0 Attachment
          Count() does NOT count NULL values in the column counted (it will however,
          count any non-NULL value -empty strings, blanks, etc.)

          Either of these statements will work.

          SELECT count( Issued ) AS [Total Insured]
          SELECT sum( CASE Issued WHEN NULL THEN 0 ELSE 1 END ) AS [Total Insured]

          They both will have the same value. And the count() is a bit faster.

          - Arnie Rowland

          "I am a great believer in luck, and I find that the harder I work, the more
          I have of it." - Thomas Jefferson (1743-1826)


          -----Original Message-----
          From: SQLQueriesNoCode@yahoogroups.com
          [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
          Sent: Tuesday, May 08, 2007 11:27 AM
          To: Arnie
          Subject: Re: [SQLQueriesNoCode] Count NON NULL values

          Actually I cannot do it like this...The reason is that I am also counting on
          other columns and I also have a group by clause.
          Here is the complete query:

          Column Type
          CODE varchar
          ISSUED datetime
          USED datetime (Nullable)
          WaitTime int
          ServeTime int


          SELECT CODE,

          SUM(CASE ISSUED WHEN NULL THEN 0 ELSE 1 END) AS [Total Issued],

          SUM(CASE USED WHEN NULL THEN 0 ELSE 1 END) AS [Total Used],

          SUM(WaitTime) AS [Total Wait Time],

          SUM(ServeTime) AS [Total Serve Time]

          FROM Table1

          GROUP BY CODE



          Regards,
          Noman Mohammad Aftab,
          Software Engineer,
          PrisLogix.


          ----- Original Message ----
          From: Saravana Gandhi K <saravana_gandhi@...>
          To: SQLQueriesNoCode@yahoogroups.com
          Sent: Monday, 7 May, 2007 11:26:00 PM
          Subject: RE: [SQLQueriesNoCode] Count NON NULL values













          Hi use the below query



          select count(Date_Column) from tblName where Date_Column is NOT NULL



          Thanks,



          Saravana



          _____



          From: SQLQueriesNoCode@ yahoogroups. com

          [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Noman Aftab

          Sent: Monday, May 07, 2007 11:20 PM

          To: SQLQueriesNoCode@ yahoogroups. com

          Subject: [SQLQueriesNoCode] Count NON NULL values



          hi all,

          I am trying to count the non-null values in a datetime column in SQL

          Server...The query is



          select SUM( CASE IssueDate WHEN NULL THEN 0 ELSE 1 END)

          from Table1



          Instead of returning the count of non-null values, it is returning the total

          number of records.



          What I am missing?



          Regards,

          Noman Mohammad Aftab,

          Software Engineer,

          PrisLogix.



          ----- Original Message ----

          From: Surya Saputra <surya.saputra@ <mailto:surya. saputra%40profes
          cipta.com>

          profescipta. com>

          To: SQLQueriesNoCode@ <mailto:SQLQueriesN oCode%40yahoogro ups.com>

          yahoogroups. com

          Sent: Monday, 7 May, 2007 9:17:35 AM

          Subject: RE: [SQLQueriesNoCode] Select in string



          Thanks all



          _____



          From: Michael Weiss [mailto:mweiss@ x5solutions. com]



          Sent: Saturday, May 05, 2007 2:57 AM



          To: SQLQueriesNoCode@ yahoogroups. com



          Subject: RE: [SQLQueriesNoCode] Select in string



          One way to do this is to use dynamic sql. Make your entire sql statement



          a string and then execute it.



          DECLARE @sqlString varchar(4000)



          DECLARE @Str varchar(1000)



          Set @Str = '''Jakarta'' ,''Bandung' ''



          SET @sqlString = 'Select City From COM_SUPPLIER Where City In ('+ @Str



          +')'



          Exec @sqlString



          <http://geo.yahoo com/serv? s=97359714/ grpId=9438758/ grpspId=17051153

          66/m



          sgId=2865/stime= 1178308902/ nc1=3848576/ nc2=3848547/ nc3=3848642>



          [Non-text portions of this message have been removed]



          <!--



          #ygrp-mlmsg {font-size:13px; font-family: arial, helvetica, clean,

          sans-serif;}

          #ygrp-mlmsg table {font-size:inherit; font:100% ;}

          #ygrp-mlmsg select, input, textarea {font:99% arial, helvetica, clean,

          sans-serif;}

          #ygrp-mlmsg pre, code {font:115% monospace;}

          #ygrp-mlmsg * {line-height: 1.22em;}

          #ygrp-text{

          font-family: Georgia;

          }

          #ygrp-text p{

          margin:0 0 1em 0;}

          #ygrp-tpmsgs{

          font-family: Arial;

          clear:both;}

          #ygrp-vitnav{

          padding-top: 10px;font- family:Verdana; font-size: 77%;margin: 0;}

          #ygrp-vitnav a{

          padding:0 1px;}

          #ygrp-actbar{

          clear:both;margin: 25px 0;white-space: nowrap;color: #666;text- align:right;
          }

          #ygrp-actbar .left{

          float:left;white- space:nowrap; }

          bld{font-weight: bold;}

          #ygrp-grft{

          font-family: Verdana;font- size:77%; padding:15px 0;}

          #ygrp-ft{

          font-family: verdana;font- size:77%; border-top: 1px solid #666;

          padding:5px 0;

          }

          #ygrp-mlmsg #logo{

          padding-bottom: 10px;}



          #ygrp-vital{

          background-color: #e0ecee;margin- bottom:20px; padding:2px 0 8px 8px;}

          #ygrp-vital #vithd{

          font-size:77% ;font-family: Verdana;font- weight:bold; color:#333;
          text-transform

          :uppercase;}

          #ygrp-vital ul{

          padding:0;margin: 2px 0;}

          #ygrp-vital ul li{

          list-style-type: none;clear: both;border: 1px solid #e0ecee;

          }

          #ygrp-vital ul li .ct{

          font-weight: bold;color: #ff7900;float: right;width: 2em;text- align:right;
          paddin

          g-right:.5em; }

          #ygrp-vital ul li .cat{

          font-weight: bold;}

          #ygrp-vital a {

          text-decoration: none;}



          #ygrp-vital a:hover{

          text-decoration: underline; }



          #ygrp-sponsor #hd{

          color:#999;font- size:77%; }

          #ygrp-sponsor #ov{

          padding:6px 13px;background- color:#e0ecee; margin-bottom: 20px;}

          #ygrp-sponsor #ov ul{

          padding:0 0 0 8px;margin:0; }

          #ygrp-sponsor #ov li{

          list-style-type: square;padding: 6px 0;font-size: 77%;}

          #ygrp-sponsor #ov li a{

          text-decoration: none;font- size:130% ;}

          #ygrp-sponsor #nc {

          background-color: #eee;margin- bottom:20px; padding:0 8px;}

          #ygrp-sponsor .ad{

          padding:8px 0;}

          #ygrp-sponsor .ad #hd1{

          font-family: Arial;font- weight:bold; color:#628c2a; font-size: 100%;line-
          height:

          122%;}

          #ygrp-sponsor .ad a{

          text-decoration: none;}

          #ygrp-sponsor .ad a:hover{

          text-decoration: underline; }

          #ygrp-sponsor .ad p{

          margin:0;}

          o {font-size:0; }

          MsoNormal {

          margin:0 0 0 0;}

          #ygrp-text tt{

          font-size:120% ;}

          blockquote{margin: 0 0 0 4px;}

          replbq {margin:4;}

          -->



          ____________ _________ _________ _________ _________ _________ _

          Yahoo! Answers - Got a question? Someone out there knows the answer. Try it

          now.

          http://uk.answers <http://uk.answers yahoo.com/> yahoo.com/



          [Non-text portions of this message have been removed]



          ********** DISCLAIMER **********

          Information contained and transmitted by this E-MAIL is proprietary to

          Sify Limited and is intended for use only by the individual or entity to

          which it is addressed, and may contain information that is privileged,

          confidential or exempt from disclosure under applicable law. If this is a

          forwarded message, the content of this E-MAIL may not have been sent with

          the authority of the Company. If you are not the intended recipient, an

          agent of the intended recipient or a person responsible for delivering the

          information to the named recipient, you are notified that any use,

          distribution, transmission, printing, copying or dissemination of this

          information in any way or in any manner is strictly prohibited. If you have

          received this communication in error, please delete this mail & notify us

          immediately at admin@sifycorp. com



          [Non-text portions of this message have been removed]














          <!--

          #ygrp-mlmsg {font-size:13px;font-family:arial, helvetica, clean,
          sans-serif;}
          #ygrp-mlmsg table {font-size:inherit;font:100%;}
          #ygrp-mlmsg select, input, textarea {font:99% arial, helvetica, clean,
          sans-serif;}
          #ygrp-mlmsg pre, code {font:115% monospace;}
          #ygrp-mlmsg * {line-height:1.22em;}
          #ygrp-text{
          font-family:Georgia;
          }
          #ygrp-text p{
          margin:0 0 1em 0;}
          #ygrp-tpmsgs{
          font-family:Arial;
          clear:both;}
          #ygrp-vitnav{
          padding-top:10px;font-family:Verdana;font-size:77%;margin:0;}
          #ygrp-vitnav a{
          padding:0 1px;}
          #ygrp-actbar{
          clear:both;margin:25px 0;white-space:nowrap;color:#666;text-align:right;}
          #ygrp-actbar .left{
          float:left;white-space:nowrap;}
          bld{font-weight:bold;}
          #ygrp-grft{
          font-family:Verdana;font-size:77%;padding:15px 0;}
          #ygrp-ft{
          font-family:verdana;font-size:77%;border-top:1px solid #666;
          padding:5px 0;
          }
          #ygrp-mlmsg #logo{
          padding-bottom:10px;}

          #ygrp-vital{
          background-color:#e0ecee;margin-bottom:20px;padding:2px 0 8px 8px;}
          #ygrp-vital #vithd{
          font-size:77%;font-family:Verdana;font-weight:bold;color:#333;text-transform
          :uppercase;}
          #ygrp-vital ul{
          padding:0;margin:2px 0;}
          #ygrp-vital ul li{
          list-style-type:none;clear:both;border:1px solid #e0ecee;
          }
          #ygrp-vital ul li .ct{
          font-weight:bold;color:#ff7900;float:right;width:2em;text-align:right;paddin
          g-right:.5em;}
          #ygrp-vital ul li .cat{
          font-weight:bold;}
          #ygrp-vital a {
          text-decoration:none;}

          #ygrp-vital a:hover{
          text-decoration:underline;}

          #ygrp-sponsor #hd{
          color:#999;font-size:77%;}
          #ygrp-sponsor #ov{
          padding:6px 13px;background-color:#e0ecee;margin-bottom:20px;}
          #ygrp-sponsor #ov ul{
          padding:0 0 0 8px;margin:0;}
          #ygrp-sponsor #ov li{
          list-style-type:square;padding:6px 0;font-size:77%;}
          #ygrp-sponsor #ov li a{
          text-decoration:none;font-size:130%;}
          #ygrp-sponsor #nc {
          background-color:#eee;margin-bottom:20px;padding:0 8px;}
          #ygrp-sponsor .ad{
          padding:8px 0;}
          #ygrp-sponsor .ad #hd1{
          font-family:Arial;font-weight:bold;color:#628c2a;font-size:100%;line-height:
          122%;}
          #ygrp-sponsor .ad a{
          text-decoration:none;}
          #ygrp-sponsor .ad a:hover{
          text-decoration:underline;}
          #ygrp-sponsor .ad p{
          margin:0;}
          o {font-size:0;}
          MsoNormal {
          margin:0 0 0 0;}
          #ygrp-text tt{
          font-size:120%;}
          blockquote{margin:0 0 0 4px;}
          replbq {margin:4;}
          -->








          ___________________________________________________________
          Yahoo! Answers - Got a question? Someone out there knows the answer. Try it
          now.
          http://uk.answers.yahoo.com/

          [Non-text portions of this message have been removed]




          Yahoo! Groups Links







          Disclaimer - May 8, 2007
          This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
          This disclaimer was added by Policy Patrol: http://www.policypatrol.com/


          [Non-text portions of this message have been removed]
        • John Warner
          Norman, just a suggestion for future questions you might have, include the fact there are other columns when you first ask your question instead of on the
          Message 4 of 7 , May 8, 2007
          • 0 Attachment
            Norman, just a suggestion for future questions you might have, include
            the fact there are other columns when you first ask your question
            instead of on the second day. You'll find the quality of answer to your
            situation will go way up if you don't keep secrets from those trying to
            assist you.

            John Warner


            > -----Original Message-----
            > From: SQLQueriesNoCode@yahoogroups.com
            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
            > Sent: Tuesday, May 08, 2007 3:47 AM
            > To: SQLQueriesNoCode@yahoogroups.com
            > Subject: Re: [SQLQueriesNoCode] Count NON NULL values
            >
            >
            > Actually I cannot do it like this...The reason is that I am
            > also counting on other columns and I also have a group by
            > clause. Here is the complete query:
            >
            > Column Type
            > CODE varchar
            > ISSUED datetime
            > USED datetime (Nullable)
            > WaitTime int
            > ServeTime int
            >
            >
            > SELECT CODE,
            >
            > SUM(CASE ISSUED WHEN NULL THEN 0 ELSE 1 END) AS [Total Issued],
            >
            > SUM(CASE USED WHEN NULL THEN 0 ELSE 1 END) AS [Total Used],
            >
            > SUM(WaitTime) AS [Total Wait Time],
            >
            > SUM(ServeTime) AS [Total Serve Time]
            >
            > FROM Table1
            >
            > GROUP BY CODE
            >
            >
            >
            > Regards,
            > Noman Mohammad Aftab,
            > Software Engineer,
            > PrisLogix.
            >
            >
            > ----- Original Message ----
            > From: Saravana Gandhi K <saravana_gandhi@...>
            > To: SQLQueriesNoCode@yahoogroups.com
            > Sent: Monday, 7 May, 2007 11:26:00 PM
            > Subject: RE: [SQLQueriesNoCode] Count NON NULL values
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            > Hi use the below query
            >
            >
            >
            > select count(Date_Column) from tblName where Date_Column is NOT NULL
            >
            >
            >
            > Thanks,
            >
            >
            >
            > Saravana
            >
            >
            >
            > _____
            >
            >
            >
            > From: SQLQueriesNoCode@ yahoogroups. com
            >
            > [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Noman Aftab
            >
            > Sent: Monday, May 07, 2007 11:20 PM
            >
            > To: SQLQueriesNoCode@ yahoogroups. com
            >
            > Subject: [SQLQueriesNoCode] Count NON NULL values
            >
            >
            >
            > hi all,
            >
            > I am trying to count the non-null values in a datetime column in SQL
            >
            > Server...The query is
            >
            >
            >
            > select SUM( CASE IssueDate WHEN NULL THEN 0 ELSE 1 END)
            >
            > from Table1
            >
            >
            >
            > Instead of returning the count of non-null values, it is
            > returning the total
            >
            > number of records.
            >
            >
            >
            > What I am missing?
            >
            >
            >
            > Regards,
            >
            > Noman Mohammad Aftab,
            >
            > Software Engineer,
            >
            > PrisLogix.
            >
            >
            >
            > ----- Original Message ----
            >
            > From: Surya Saputra <surya.saputra@ <mailto:surya.
            > saputra%40profes cipta.com>
            >
            > profescipta. com>
            >
            > To: SQLQueriesNoCode@ <mailto:SQLQueriesN oCode%40yahoogro ups.com>
            >
            > yahoogroups. com
            >
            > Sent: Monday, 7 May, 2007 9:17:35 AM
            >
            > Subject: RE: [SQLQueriesNoCode] Select in string
            >
            >
            >
            > Thanks all
            >
            >
            >
            > _____
            >
            >
            >
            > From: Michael Weiss [mailto:mweiss@ x5solutions. com]
            >
            >
            >
            > Sent: Saturday, May 05, 2007 2:57 AM
            >
            >
            >
            > To: SQLQueriesNoCode@ yahoogroups. com
            >
            >
            >
            > Subject: RE: [SQLQueriesNoCode] Select in string
            >
            >
            >
            > One way to do this is to use dynamic sql. Make your entire
            > sql statement
            >
            >
            >
            > a string and then execute it.
            >
            >
            >
            > DECLARE @sqlString varchar(4000)
            >
            >
            >
            > DECLARE @Str varchar(1000)
            >
            >
            >
            > Set @Str = '''Jakarta'' ,''Bandung' ''
            >
            >
            >
            > SET @sqlString = 'Select City From COM_SUPPLIER Where City In ('+ @Str
            >
            >
            >
            > +')'
            >
            >
            >
            > Exec @sqlString
            >
            >
            >
            > <http://geo.yahoo com/serv? s=97359714/ grpId=9438758/
            > grpspId=17051153
            >
            > 66/m
            >
            >
            >
            > sgId=2865/stime= 1178308902/ nc1=3848576/ nc2=3848547/ nc3=3848642>
            >
            >
            >
          Your message has been successfully submitted and would be delivered to recipients shortly.