Post reply

How to use a single statement ?

  • September 6, 2006 at 8:54 am

    #103229

    Hi,

     

    I have 2 SQL statements :

     

    Select  count(A)  as total       from TABLE  

    Select  count(A)  as sla_ok   from TABLE  where A=’1’       (A is a Boolean)

     

    The result is like

        total

    1 6958

     

       sla_ok

    1 6764

     

    I want to a result like   :

     

    Total             sla_ok             %  (sla_ok / total)

    6958              6764                0,97

     

    How can I achieve that , using a single statement ?

     

    Thanks in advance …

  • September 6, 2006 at 8:56 am

    #658764

    SELECT COUNT(A) AS Total , SUM(CAST (A AS INT)) AS SLA_OK

    FROM TABLE

    LinkedIn Profile

  • September 6, 2006 at 9:10 am

    #658769

    Hummm,

     

    I’m sorry I didn’t understand the cast part. Why convert the type ?

     

    I just want to select the total set from a table , and a part of the set  onde the same row….

  • September 6, 2006 at 9:15 am

    #658771

    You can't sum on a bit datatype as far as I remember!!

     

    Anyways isn't this what you needed?

  • September 6, 2006 at 9:18 am

    #658772

    No, I dont want to sum  want to COUNT, the type is char   ‘0’ ‘1’ ‘A’…

  • September 6, 2006 at 9:27 am

    #658775

    So you have a boolean char .

     

    SELECT COUNT(A) AS Total , SUM(CASE WHEN 'A' = '1' THEN 1 ELSE 0 END) AS SLA_OK FROM TABLE

  • September 6, 2006 at 9:45 am

    #658780

    Hi,

    Ok, assume it´s not a boolean ...

    i just wanto to count one set if A='X' and the total set

     

  • September 6, 2006 at 9:48 am

    #658781

    Did you try my query??? It's what it's supposed to do!

  • September 6, 2006 at 9:57 am

    #658790

    ok i made on adapation and it worked fine.

    Tkanks a lot!

     

  • September 6, 2006 at 10:00 am

    #658791

    Oops, ya works better like that

    SUM(CASE WHEN A = '1'

  • September 6, 2006 at 10:05 am

    #658792

    One thing more- I want to create a third column with the result of the expression cdu_SLA_OK/Total

     

    SELECT COUNT(cdu_slaok) AS Total , SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)

    AS SLA_OK, result = SLA_OK/Total

     

    But i get the error :

     

    Server: Msg 207, Level 16, State 3, Line 1

    Invalid column name 'SLA_OK'.

    Server: Msg 207, Level 16, State 1, Line 1

    Invalid column name 'Total'.

     

  • September 6, 2006 at 10:37 am

    #658802

    You can't do that on sql server like Acces let you do.  You must reuse the count and sum operations for the total >>

     

    SELECT COUNT(cdu_slaok) AS Total , SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)

    AS SLA_OK, SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END) / COUNT(cdu_slaok)*1.0 AS Total FROM...

     

    the 1.0 is to cast to a decimal so that you don't always get 0 for the total column

     

  • September 6, 2006 at 10:45 am

    #658805

    i get .0 

  • September 6, 2006 at 10:54 am

    #658808

    Doh, operations priority... the cast is made after the division; easy fix :

     

    SELECT 1.0 * COUNT(cdu_slaok) AS Total , SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)

    AS SLA_OK, SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END) / COUNT(cdu_slaok) AS Total FROM...

  • September 6, 2006 at 11:17 am

    #658815

    same problem

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply