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!

Which version of MySQL are you running?

GMB
– GMB

2020-01-21 12:44:47 +00:00
Commented Jan 21, 2020 at 12:44 — GMB
– GMB, Commented Jan 21, 2020 at 12:44

Strawberry · Accepted Answer · 2020-01-21 12:45:29Z

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 |
+----+-------+----+

answered Jan 21, 2020 at 12:45

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-01-21 12:42:26Z

0

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;

answered Jan 21, 2020 at 12:42

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Mysql query with SELECT in WHERE

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related