query one table three for three different where variables

Question

query one table three for three different where variables

October 4, 2022 at 3:58 pm

This was removed by the editor as SPAM
October 4, 2022 at 3:59 pm

This was removed by the editor as SPAM

Viewing 7 posts - 46 through 51 (of 51 total)

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

brian.cook Default port Points: 1484 More actions · Answer 1

Jeffrey, If I remove FailureCount from both the Select and Group By, I get the following error;

Column FailureCount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Thanks!

Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

brian.cook Default port Points: 1484 More actions · Answer 2

Jeffrey, Okay, I removed the FailureCount from the Select and Group By. I also removed Failures from the Order by statement.

That puts it in to a single row. So, now I have to discuss with the boss if he still wants the Failure Count total in the report or if this works for him.

Thanks!

Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

brian.cook Default port Points: 1484 More actions · Answer 3

Jeffrey, so talked to the boss. he does want a total failure count and may want a Diff column too later. I will work on that. If I get stuck, I will ask for help at that point.

Thank you!

Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 4

brian.cook wrote:

Jeffrey, so talked to the boss. he does want a total failure count and may want a Diff column too later. I will work on that. If I get stuck, I will ask for help at that point.
Thank you!

For a grand total - add the column TotalFailures = sum(FailureCount). For a diff column - you need to determine what to check for the diff. Is that the difference between 1st and 15th - difference between 1st and last - or separate columns showing difference between 1st and 15th and 15th and last?

Assuming difference between 1st and last:

, TotalDIFF = sum(Case When DAY = @last_day Then FailureCount Else 0 END) - sum(Case When DAY = 1 Then FailureCount Else 0 End)

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

brian.cook Default port Points: 1484 More actions · Answer 5

Jeffrey! Thanks, the Total Failures one I had. I was struggling with the Total Diff.

Thank you ever so much!

Have a great afternoon!

Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner