How to get aggregates without using a nested sql query

Question

I am writing a custom report from an Avamar (Postgresql) database which contains backup job history. My task is to display jobs that failed last night (based on status_code), and include that client's success ratio (jobs succeeded/total jobs run) over the past 30 days on the same line.

So the overall select just picks up clients that failed (status_code doesn't equal 30000, which is the success code). However, for each failed client from last night, I need to also know how many jobs have succeeded, and how many jobs total were started/scheduled in the past 30 days. (The time period part is simple, so I haven't included it in the code below, to keep it simple.)

I tried to do this without using a nested query, based on Hobodave's feedback on this similar question but I'm not quite able to nail it.

In the query below, I get the following error: column "v_activities_2.client_name" must appear in the GROUP BY clause or be used in an aggregate function

Here's my (broken) query. I know the logic is flawed, but I'm coming up empty with how best to accomplish this. Thanks in advance for any guidance!

select
  split_part(client_name,'.',1) as client_name,
  bunchofothercolumnns,
  round(
    100.0 * (
      ((sum(CASE WHEN status_code=30000 THEN 1 ELSE 0 END))) /
      ((sum(CASE WHEN type='Scheduled Backup' THEN 1 ELSE 0 END))))
    as percent_total
from v_activities_2
  where
    status_code<>30000
  order by client_name

Answer 1

You need to define a GROUP BY if you have columns in the SELECT that do not have aggregate functions performed on them:

  SELECT SPLIT_PART(t.client_name, '.', 1) AS client_name,
         SUM(CASE WHEN status_code = 30000 THEN 1 ELSE 0 END) as successes
    FROM v_activities_2
GROUP BY SPLIT_PART(t.client_name, '.', 1)
ORDER BY client_name

How do you expect the following to work:

      SUM(CASE WHEN status_code = 30000 THEN 1 ELSE 0 END) as successes
 FROM v_activities_2
WHERE status_code <> 30000

You can't expect to count rows you're excluding.

Answer 2

Why avoid nested query?

It seems most logical / efficient solution here.

If you do this in one pass with no sobqueries (only group by's), you will end with scanning the whole table (or joined tables) - which is not efficient, because only SOME clients failed last night.

Subqueries are not that bad, in general.