PHP/SQL select based on sum result

Question

I'm having a hard time limiting my query results based on sum. Example code:

    $rows = Entry::find()
      ->section('cities')
      ->select('state')
      ->having("sum(case when covered = '1' then 1 else 0 end) = 0")
      ->asArray()
      ->all();

I want to only select states where 0 cities are "covered". Running my code above the "having" line seems to be ignored (it includes both zero and non-zero cases). I tried using "where" instead of "having" but it results in a PDOException - "Invalid use of group function". I assume "having" is the right approach, but that I'm making a novice mistake -- any tips?

/*** UPDATE 1 ***/

Thanks @scaisEdge and @angelm for the tip -- groupBy helps, but it's still not working as expected. It seems the "having" line is still ignored. With modified code:

    $test = Entry::find()
      ->section('cities')
      ->select(['state', 'covered', "sum(case when covered = '1' then 1 else 0 end) as numCovered"])
      ->groupBy('state')
      ->having("sum(case when covered = '1' then 1 else 0 end) = 0")
      ->asArray()
      ->all();

I log the following results:

{state: "AL", covered: "0", numCovered: "0"}
{state: "AK", covered: "0", numCovered: "0"}
{state: "CA", covered: "1", numCovered: "19"}
{state: "CO", covered: "0", numCovered: "0"}
...

As you can see above, states (CA) are included when numCovered is clearly not 0.

I also tried the following code for "having" (which I assume is the same):

->having("numCovered = 0")

/*** UPDATE 2 ***/

Using a reduced test case suggested by @cpalmer still results in "CA" being selected despite having numCovered = 19. I'm now wondering if this is a peculiarity with Craft CMS, since it would seem my query is correct?

    $test = Entry::find()
      ->section('cities')
      ->select('state')
      ->groupBy('state')
      ->having("sum(case when covered = '1' then 1 else 0 end) = 0")
      ->asArray()
      ->all();

Is there a way to write this query without having?

/*** UPDATE 3 ***/

As suggested by the DB Fiddle posted by @pocketrocket my sql should work. Dumping the raw sql suggests the having line is ignored. The issue likely resides with CraftCMS/Yii and my lack of understanding of the environment.

Answer 1

First of all: I am total with @Olivier, you should decouple the SQL part of your question from the architecture itself. For the SQL part, it's important to let others know which database or SQL dialect you use (MySQL, PostgreSQL, MsSQL...).

Just guessing it's MySQL what you are using: Both ways should actually work, repeating the querypart in having or referencing it by name as suggested by user126587

If both don't work and you would like to work without having maybe you can implement a subselect?

SELECT 
    state, 
    sum(case when covered = '1' then 1 else 0 end) as numCovered 
FROM cities 
GROUP BY state
HAVING sum(case when covered = '1' then 1 else 0 end) = 0;

SELECT 
    state, 
    sum(case when covered = '1' then 1 else 0 end) as numCovered 
FROM cities 
GROUP BY state
HAVING numCovered = 0;

SELECT * FROM (
  SELECT 
      state, 
      sum(case when covered = '1' then 1 else 0 end) as numCovered 
  FROM cities 
  GROUP BY state
) sub_select
WHERE sub_select.numCovered = 0;

You can play around with it here: DB Fiddle Link