How to use a single statement ?

  • And folks, that's why you should always post the code you put on the forums.

     

    But I'm sure you can figure out why this doesn't work by yourself now.

  • Will this help..?

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

    CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) AS Total

    FROM TABLE


    Regards,

    Ganesh

  • Yes. Thanks!

  • And if i want to compare the value for total , for example total > 80 ?

    If i do so i get an error... it must be because, its a temporary variable , is possible to do it ?

     

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

    CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) AS Total

    FROM

     processos where cdu_fechado='1'and dataabertura >='2006-01-01'and

    cliente not in ('PRIMAVERA', 'TESTES', '_ESCOLAS', 'AGERE', 'CMFUNDAO', 'FIRST', 'IPCA',

    'PCOSTA','RUP', 'SASUM')

    group by cliente

    order by cliente  

  • You have 2 columns name Total... that might be the problem.

  • Changed the last column to "PERC", but is still get the message:

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

    Invalid column name 'PERC'.

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

    Invalid column name 'PERC'.

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

    Invalid column name 'PERC'.

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

    Invalid column name 'PERC'.

  • Please post the entire query... most message by themselves are not very easy to debug... unless you only want vague pointers .

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

    CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) AS PERC

    FROM

     processos where cdu_fechado='1'and dataabertura >='2006-01-01'and

    cliente not in ('PRIMAVERA', 'TESTES', '_ESCOLAS', 'AGERE', 'CMFUNDAO', 'FIRST', 'IPCA',

    'PCOSTA','RUP', 'SASUM')

    group by cliente

    order by cliente

  • There's no syntaxe error in that query.  Post the query that is giving you the error.

  • If you are getting a syntax error on that, run this and give us the results:

    SELECT SERVERPROPERTY('productlevel'),

    SERVERPROPERTY('productversion'),

    SERVERPROPERTY('edition')

    -SQLBill

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

    CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) AS PERC

    FROM

     processos where cdu_fechado='1'and dataabertura >='2006-01-01'and

    cliente not in ('PRIMAVERA', 'TESTES', '_ESCOLAS', 'AGERE', 'CMFUNDAO', 'FIRST', 'IPCA',

    'PCOSTA','RUP', 'SASUM') and PERC < 80

    group by cliente

    order by cliente  

  • Result of

    SELECT SERVERPROPERTY('productlevel'),

    SERVERPROPERTY('productversion'),

    SERVERPROPERTY('edition')

    SP4 8.00.2039 Enterprise Edition

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

    CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) AS PERC

    FROM

     processos where cdu_fechado='1'and dataabertura >='2006-01-01'and

    cliente not in ('PRIMAVERA', 'TESTES', '_ESCOLAS', 'AGERE', 'CMFUNDAO', 'FIRST', 'IPCA',

    'PCOSTA','RUP', 'SASUM')

    group by cliente

    having CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) < 80

    order by cliente  

  • Great. The statement works just fine.

     

    Now I want to use the result of this query as an input to select certain rows ..a subquery….

     

    I have tried to use the following clauses : any , exists , in … but nothing works ….i thinks the more appropriate to this case is exists, maybe I have forgot something… could you take a look:

     

    select  cliente, processo from processos where dataabertura >='2006-01-01' and

    --cliente=any

    cliente exists

    --cliente in

     (

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

    CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) AS PERC

    FROM

     processos where cdu_fechado='1'and dataabertura >='2006-01-01'and

    cliente not in ('PRIMAVERA', 'TESTES', '_ESCOLAS', 'AGERE', 'CMFUNDAO', 'FIRST', 'IPCA',

    'PCOSTA','RUP', 'SASUM')

    group by cliente

    having CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) < 80

    --order by cliente)

    )

     

    Error with exists:

    Server: Msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword 'exists'.

     

    Error with any:

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

    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

     

    Error with in:

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

    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

  • Ok i found the problem, now it works :

    select  cliente, processo, cdu_slaok from processos where dataabertura >='2006-01-01' and

    cdu_fechado='1' and

    --cliente=any

    --cliente exists

    cliente in

    (

    SELECT cliente

    FROM

     processos where cdu_fechado='1'and dataabertura >='2006-01-01'and

    cliente not in ('PRIMAVERA', 'TESTES', '_ESCOLAS', 'AGERE', 'CMFUNDAO', 'FIRST', 'IPCA',

    'PCOSTA','RUP', 'SASUM')

    group by cliente

    having CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) < 80

    )

    order by cliente

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

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