Question 20584 in Mysql Question posted in
The official MySQL documentation can be found here.

Having clause with aggregate and NOT IN doesn't apply both as expected – Mysql

I’m trying to select trees from the following sample that have had the Vigor value 6,8 or 9 for the past three years straight. So, in the below grid I would want all rows with TreeID 2 and 3, but not any with TreeID 1.

PlotID    ObsYear TreeID Vigor
MACFI0407 2020    1      8
MACFI0407 2021    1      8
MACFI0407 2022    1      8
MACFI0407 2020    2      1
MACFI0407 2021    2      1
MACFI0407 2022    2      8
MACFI0407 2020    3      1
MACFI0407 2021    3      1
MACFI0407 2022    3      1

This is what I thought I wanted, but it doesn’t seem like mySQL is using the ‘AND’ to mean BOTH. This is leaving out TreeID 3 since it has a count of 3 (but Vigor value was 1)

SELECT PlotID, TreeID, Vigor, count(*) c
FROM tblTreeInfo
GROUP BY PlotID, TreeID, Vigor
HAVING (c < 3 AND Vigor NOT IN (6, 8, 9))

So I tried this, but this leaves out TreeID 2, since it has Vigor 8, but only for 1 year.

SELECT PlotID, TreeID, Vigor, count(*) c
FROM tblTreeInfo
WHERE Vigor NOT IN (6, 8, 9)
GROUP BY PlotID, TreeID, Vigor
HAVING (c < 3)

How can I get rows with both vigor not 6,8,9 AND c<3? Thank you!

Tags:

3

Answers


  1. 0

    You want the last three years (2020 to 2022). Getting these rows is the first step. Then you want to omit tree IDs that had vigor 6, 8 or 9 in each of these years. Find these IDs, by counting distinct years in which these vigors occured. Then select the rows omitting these IDs.

    with rows_of_interest as
(
  select *
  from mytable
  where obsyear between year(curdate()) - 3 and year(curdate()) - 1
)
select *
from rows_of_interest
where treeid not in
(
  select treeid
  from rows_of_interest
  where vigor in (6, 8, 9)
  group by treeid
  having count(distinct obsyear) = 3
)
order by treeid, obsyear;

    Demo: https://dbfiddle.uk/DeRhqRoX

    Login or Signup to reply.
  2. 0 
    ## TREES THAT ARE NOT VIGOROUS OVER PAST 3 YEARS
SELECT
  T.*
FROM mytable AS T
LEFT JOIN (
        SELECT treeid
        FROM mytable
        GROUP BY treeid
        HAVING (MAX(CASE WHEN obsyear = year(curdate()) - 3 AND vigor IN (6,8,9) THEN 1 ELSE 0 END)
              + MAX(CASE WHEN obsyear = year(curdate()) - 2 AND vigor IN (6,8,9) THEN 1 ELSE 0 END)
              + MAX(CASE WHEN obsyear = year(curdate()) - 1 AND vigor IN (6,8,9) THEN 1 ELSE 0 END)) = 3
        ) AS VIGOR ON T.TREEID = VIGOR.TREEID
WHERE VIGOR.TREEID IS NULL
    plotid obsyear treeid vigor
    MACFI0407 2020 2 1
    MACFI0407 2021 2 1
    MACFI0407 2022 2 8
    MACFI0407 2020 3 1
    MACFI0407 2021 3 1
    MACFI0407 2022 3 1
    MACFI0407 2020 4 6
    MACFI0407 2020 4 8
    MACFI0407 2020 4 9 
    ## TREES THAT ARE VIGOROUS OVER PAST 3 YEARS
SELECT
  T.*
FROM mytable AS T
INNER JOIN (
        SELECT treeid
        FROM mytable
        GROUP BY treeid
        HAVING (MAX(CASE WHEN obsyear = year(curdate()) - 3 AND vigor IN (6,8,9) THEN 1 ELSE 0 END)
              + MAX(CASE WHEN obsyear = year(curdate()) - 2 AND vigor IN (6,8,9) THEN 1 ELSE 0 END)
              + MAX(CASE WHEN obsyear = year(curdate()) - 1 AND vigor IN (6,8,9) THEN 1 ELSE 0 END)) = 3
        ) AS VIGOR ON T.TREEID = VIGOR.TREEID
    plotid obsyear treeid vigor
    MACFI0407 2020 1 8
    MACFI0407 2021 1 8
    MACFI0407 2022 1 8
    MACFI0407 2020 5 6
    MACFI0407 2021 5 8
    MACFI0407 2022 5 9
    MACFI0407 2023 5 9
    MACFI0407 2020 5 6
    MACFI0407 2020 5 8
    MACFI0407 2022 5 9
    MACFI0407 2022 5 9

    fiddle

    Login or Signup to reply.
  3. 0

    A Simple way to do it, is to include count(*) = 3 since only Trees with last 3 years having Vigor IN (6, 8, 9) must be ignored :

    with cte as (
  SELECT PlotID, TreeID, Vigor, count(*) c
  FROM mytable
  GROUP BY PlotID, TreeID, Vigor
  HAVING (c <= 3 AND Vigor NOT IN (6, 8, 9))
)
select t.*
from mytable t
inner join cte c on c.TreeID = t.TreeID

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search