Mysql query with SELECT in WHERE

Question

I have table 'leads' with columns 'lead_id', 'bearbeitungs_id_einkauf', 'ep'

Example:

lead_id     bearbeitungs_id_einkauf   ep

1           12345                      10
2           12345                      20
3           77777                      5
4           88888                      8

I need get all records where 'ONT' > 20 . ONT is sum 'ep' grouped by 'bearbeitungs_id_einkauf' :

'12345' = 30
'77777' = 5
'88888' = 8

So as result i need show only these records:

lead_id     bearbeitungs_id_einkauf   ep

1           12345                      10
2           12345                      20

I tried this:

SELECT l.lead_id, l.bearbeitungs_id_einkauf, l.ep
FROM leads as l
WHERE (SELECT SUM(ep) as ONT FROM leads l2
       WHERE l2.bearbeitungs_id_einkauf = l.bearbeitungs_id_einkauf) > 20;

But cant understand is it correct? And how i can show ONT? Thanks!

Answer 1

Consider the following:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,m VARCHAR(12) NOT NULL
,n INT NOT NULL
);

INSERT INTO my_table VALUES
(1,'12345',10),
(2,'12345',20),
(3,'77777',5),
(4,'88888',8);

SELECT * FROM my_table;
+----+-------+----+
| id | m     | n  |
+----+-------+----+
|  1 | 12345 | 10 |
|  2 | 12345 | 20 |
|  3 | 77777 |  5 |
|  4 | 88888 |  8 |
+----+-------+----+

SELECT x.* 
  FROM my_table x 
  JOIN 
     ( SELECT m
            , SUM(n) total 
         FROM my_table 
        GROUP 
           BY m 
       HAVING total > 20
     ) y 
    ON y.m = x.m;
+----+-------+----+
| id | m     | n  |
+----+-------+----+
|  1 | 12345 | 10 |
|  2 | 12345 | 20 |
+----+-------+----+

Answer 2

You can use window functions:

SELECT l.lead_id, l.bearbeitungs_id_einkauf, l.ep, l.ont
FROM (SELECT l.lead_id, l.bearbeitungs_id_einkauf, l.ep,
             SUM(l.ep) OVER (PARTITION BY l.lead_id) as ont
      FROM leads l 
     ) l
WHERE ont > 20;