CODEWITHSUNDEEP
mysqlsqlgroup-bysum
Tomasz Jureczko
Tomasz Jureczko

Reputation: 167

mysql select query issue

We have a DB like this:

CREATE TABLE `jobs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `job` varchar(255),
  PRIMARY KEY (`id`)
);

INSERT INTO `jobs` VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d');

CREATE TABLE `payments` (
  `job_id` int,
  `amount` int 
);

INSERT INTO `payments` VALUES
(1,100),
(1,100),
(2,600),
(2,600);

Our task is:

Get all jobs, where sum of payments is smaller than 1000.

As a result we should jobs 'a','c' and 'd'. But our query:

SELECT job 
FROM jobs j
JOIN payments p ON j.id=p.job_id
GROUP BY job_id
HAVING sum(amount) < 1000;

excludes jobs without any payments. So a result we get only 'a'.

How should we construct the query to get all jobs where sum of payments is smaller than 1000?

Upvotes: 1

Views: 122

Answers (3)

steve godfrey
steve godfrey

Reputation: 1234

left join will work, as long as you use a case statement to ensure that the amount is calculated as zero for jobs with no payments. Otherwise, amount will be null, and therefore cannot be compared to 1000 in the HAVING clause.

SELECT j.id job_id ,sum(case when amount is null then 0 else amount end)
FROM jobs j LEFT JOIN payments p ON j.id=p.job_id
GROUP BY j.id
HAVING sum(case when amount is null then 0 else amount end) < 1000;

N.B. this works on oracle, not sure about exact syntax on mysql.

Upvotes: 0

Bohemian
Bohemian

Reputation: 425083

You need to join job to the result of the sum query:

SELECT * 
from job j
join (SELECT job_id, sum(amount) 
    FROM jobs j
    JOIN payments p ON j.id=p.job_id
    GROUP BY job_id
    HAVING sum(amount) < 1000) x on x.job_id = j.job_id
union
SELECT * from job where job_id not in (select job_id from payments);

The union also finds jobs that have NO payments

Upvotes: 0

Dirk
Dirk

Reputation: 3093

I think you might need a LEFT JOIN

SELECT job 
FROM jobs j LEFT JOIN payments p ON j.id=p.job_id
GROUP BY job_id 
HAVING sum(amount) < 1000;

Upvotes: 2

Related Questions