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

RE: [SQLQueriesNoCode] Count NON NULL values

Expand Messages
  • Saravana Gandhi K
    Hi use the below query select count(Date_Column) from tblName where Date_Column is NOT NULL Thanks, Saravana _____ From: SQLQueriesNoCode@yahoogroups.com
    Message 1 of 7 , May 7 11:26 AM
    • 0 Attachment
      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%40profescipta.com>
      profescipta.com>
      To: SQLQueriesNoCode@ <mailto:SQLQueriesNoCode%40yahoogroups.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@...


      [Non-text portions of this message have been removed]
    • 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 2 of 7 , May 8 12:46 AM
      • 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 3 of 7 , May 8 6:29 AM
        • 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 4 of 7 , May 8 1:10 PM
          • 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 5 of 7 , May 8 1:23 PM
            • 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.