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.

you need to use 'group by' when using keyword 'having' .. I think in your case you'd need to group by 'covered' — Angel M., Jul 07 '20 at 07:34
Why don't you test your query directly with phpMyAdmin? The CMS has nothing to do with that. — Olivier, Jul 10 '20 at 07:23

score 2 · Answer 1 · answered Jul 07 '20 at 09:59

2

try add a groupBy() for state

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

answered Jul 07 '20 at 09:59

ScaisEdge

131,976
10
91
107

Thanks for putting me on the right track, your answer helped, but still not quite there -- see my updated question/results above please, and thanks again. – calipoop Jul 07 '20 at 17:01

score 2 · Accepted Answer · answered Jul 13 '20 at 00:30

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

Thanks for answer, it was the most help. As you demonstrated by the DB Fiddle Link, all 3 of your suggestions should work. I believe the issue I'm having is with CraftCMS and Yii -- using a sqldump suggests that the `having` line is ignored. Your subselect seems a good alternative. Thanks also for introducing me to DB Fiddle! — calipoop, Jul 16 '20 at 01:42

score 1 · Answer 3 · answered Jul 10 '20 at 00:11

Try removing 'covered' from your selected columns. In some SQL databases, when you group your data, simply having a column in your select statement that isn't in the group by statement or in an aggregate can cause an error. In your case, I think this column is causing the unexpected results. If you really want to include it, throw it into an aggregate and alias it, like 'MAX(covered) as covered'

$test = Entry::find()
      ->section('cities')
      ->select(['state', "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();

thanks for the suggestion -- unfortunately that wasn't it... I even tried removing all but the 'state' column. I'm now wondering if this is an issue with Craft CMS, since it would appear my query is otherwise correct... — calipoop, Jul 10 '20 at 04:53

score 1 · Answer 4 · answered Jul 10 '20 at 10:05

1

Try putting in the having part "numCovered = 0".

answered Jul 10 '20 at 10:05

lsd

504
1
4
16

score 0 · Answer 5 · answered Jul 14 '20 at 11:48

Well my guess is you are forgetting a single quote around the 0 in your having clause.

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

PHP/SQL select based on sum result

5 Answers5