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) A socialist is someone who will give you the shirt off *someone else's* back.
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
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