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

Mysql – Keep only groups in a SQL view that have at least one bigger value then a certain condition

I have SQL view data_start with 3 columns

CALMONTH, COSTCENTER, Kontonummer

Sample data:

CALMONTH COSTCENTER Kontonummer
-------------------------------
202301      A           AA
202302      A           AB
202101      B           BA
202012      B           BB
202204      C           CA
202312      C           CB
...

How can I keep only the rows per COSTCENTER group, which CALMONTH maximum is greater than a specific value.

So for example I only want to keep the COSTCENTER where at least one CALMONTH is greater than 202201.

The output should be:

CALMONTH COSTCENTER Kontonummer
-------------------------------
202301      A           AA
202302      A           AB
202204      C           CA
202312      C           CB
...

I tried this SQL statement:

SELECT 
    "CALMONTH", "COSTCENTER", "Kontonummer"
GROUP BY
    ("COSTCENTER") %>%
    SERIES_FILTER(max("CALMONTH") > 202201) %>%,
FROM "data_start"

But this results in an error:

Error Extraneous BY, expecting ‘,’, FROM

The SQL is in the SAP HANA CLOUD and called SQLSCRIPT, which has the syntax from mysql.

Tags:

2

Answers


  1. 0

    This can be done using group by and having :

    First identify COSTCENTER having CALMONTH maximum is greater than 202201 :

      select COSTCENTER
  from data_start
  group by COSTCENTER
  having max(CALMONTH) > 202201

    Then join this dataset with the table to get the expected output :

    select t.*
from data_start t
inner join (
  select COSTCENTER
  from data_start
  group by COSTCENTER
  having max(CALMONTH) > 202201
) as s on s.COSTCENTER = t.COSTCENTER;

    Demo on mysql here

    Login or Signup to reply.
  2. 0

    You can also make use of CTE Common_table_expression
    to achieve same result.

    WITH NewCostCenter AS (
  SELECT COSTCENTER
  FROM data_start
  GROUP BY COSTCENTER
  HAVING MAX(CALMONTH) > 202201
)
SELECT t.*
FROM data_start t
INNER JOIN NewCostCenters s ON s.COSTCENTER = t.COSTCENTER;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search