result in same line

  • patdev99

    SSC Rookie

    Points: 33

    Hello,

     

    trying to query the result by success or failure in 2 seperate columns as bellow but not getting result in one line.

    SELECT distinct $month_of_year, $day_of_month,

    (CASE

    WHEN status ='success' THEN count(status)

    ELSE 0

    END) as Success,

    (CASE

    WHEN status like '%fail%' THEN count(status)

    ELSE 0

    END) as Failed

    from $log

    WHERE action = 'login'

    group by $month_of_year, $day_of_month,status

    order by $day_of_month

    result comes as follow:

     

    to_char to_char success failed

    2019-11 03 0 29

    2019-11 04 0 138

    2019-11 04 19 0

    2019-11 05 0 166

    2019-11 05 16 0

    i want to  to be as this:

    to_char  to_char success failed

    2019-11  03           0             29

    2019-11  04           19         138

    2019-11  05           16        166

    what am i doing wrong.

     

     

  • x

    SSC-Insane

    Points: 23484

    It always helps to include sample data so that people can work with your code and make changes to see what would work.

    Anyways:

    you are grouping by status, so that introduces an extra line.

     

    You could try something like this, however I don't know if what I typed will work because you provided no test data, and of course my laziness is probably a contributing factor too 🙂

    -- FORMATTED MORE THAN NECESSARY TO SHOW CASE EXPRESSIONS FOR CLARITY.
    SELECT distinct $month_of_year, $day_of_month,
    SUM
    (
    CASE
    WHEN status ='success' THEN 1 ELSE 0 END
    )
    as Success,
    SUM
    (
    CASE
    WHEN status like '%fail%' THEN 1 ELSE 0 END
    ) as Failed
    from $log
    WHERE action = 'login'
    group by $month_of_year, $day_of_month
    order by $day_of_month

  • ScottPletcher

    SSC Guru

    Points: 98206

    No usable sample data, so I can't test it, but this should give you the results you want:

    SELECT $month_of_year, $day_of_month,

    SUM(CASE WHEN status ='success' THEN 1 ELSE 0 END) as Success,
    SUM(CASE WHEN status LIKE '%fail%' THEN 1 ELSE 0 END) as Failed

    FROM $log

    WHERE action = 'login'

    GROUP BY $month_of_year, $day_of_month --,status EDIT:meant to remove this before!

    ORDER BY $day_of_month

    • This reply was modified 3 weeks, 4 days ago by  ScottPletcher.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88170

    The code here isn't for SQL Server - it looks like it might be for MySQL.  This forum is for Microsoft SQL Server - you would probably get a better answer from a forum that is specific to your database product.

    With that said...

     Select Distinct                                                           $month_of_year
    , $day_of_month
    , sum(Case When status = 'success' Then 1 Else 0 End) As Success
    , sum(Case When status Like '%fail%' Then 1 Else 0 End) As Failed
    From $log
    Where Action = 'login'
    Group By
    $month_of_year
    , $day_of_month
    , status
    Order By
    $day_of_month

    I would also recommend adding another column to show the total count - which would then include anything that isn't a 'success' or 'failure'.  For example - completed, in progress, idle, waiting or any other status...

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Sumathi

    SSC Rookie

    Points: 28

    Just remove status from select clause and see.

    • This reply was modified 3 weeks, 4 days ago by  Sumathi.
  • patdev99

    SSC Rookie

    Points: 33

    status" must appear in the GROUP BY clause or be used in an aggregate function

    trying to get success and failed status count in same line instead getting 2 records for each day..

     

    thanks

  • x

    SSC-Insane

    Points: 23484

    patdev99 wrote:

    status" must appear in the GROUP BY clause or be used in an aggregate function

    trying to get success and failed status count in same line instead getting 2 records for each day..

    thanks

    I would put "status" in the aggregates, which in this case it would be used in the case statement. Then I would remove it from the group by. The code I posted would seem to be ok for SQL Server, but if that's not what you're using it might not be compatible.

     

Viewing 7 posts - 1 through 7 (of 7 total)

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