November 11, 2019 at 8:45 pm
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.
November 11, 2019 at 9:20 pm
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
November 11, 2019 at 9:20 pm
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
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 11, 2019 at 9:24 pm
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...
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
November 11, 2019 at 9:32 pm
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
November 11, 2019 at 9:47 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply