CODEWITHSUNDEEP
mysqlsql
Richard
Richard

Reputation: 733

SQL select with conditions to find other record with better value

I have the following table with some data:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";


CREATE TABLE `activities` (
  `id` int(10) UNSIGNED NOT NULL,
  `project_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `task_hour` double(8,2) NOT NULL,
  `validated` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `activities` (`id`, `project_id`, `user_id`, `task_hour`, `validated`) VALUES
(1, 1, 1, 10.00, 1),
(2, 1, 1, 20.00, 0),
(3, 2, 1, 5.00, 1),
(4, 3, 1, 30.00, 0);

When I do a SELECT user_id,project_id,task_hour,validated FROM activities, here is what I get:

| user_id | project_id | task_hour | validated |
|---------|------------|-----------|-----------|
|       1 |          1 |        10 |      true |
|       1 |          1 |        20 |     false |
|       1 |          2 |         5 |      true |
|       1 |          3 |        30 |     false |

I would like to get the following result from a select:

| user_id | task_hour_total |
|---------|-----------------|
|       1 |              45 |

This result comes from the sum of task_hour for user 1 with the condition that the task_hour can be added only if validated is true OR in case validated is false, that there is not a record in the table for the same user_id and project_id with validated is true.

So the reasoning for each line would be:

| user_id | project_id | task_hour | validated |
|---------|------------|-----------|-----------|
|       1 |          1 |        10 |      true | -> include in the sum because validated is true
|       1 |          1 |        20 |     false | -> do not include in the sum because validated is false and there is the first record which has same user_id, same project_id and validated is true
|       1 |          2 |         5 |      true | -> include in the sum because validated is true
|       1 |          3 |        30 |     false | -> include in the sum because validated is false and there is no record in this table for user_id 1 and project_id 3 where validated is true

I have tried the following but it tells me that this is not the right structure in mysql. This is a first test to get a column to say if it found another record in the db with validated = true for same user_id and project_id:

select @u = user_id, @p = project_id,task_hour,validated
case when (select count(*) from activities where user_id = @u and project_id = @p and validated = true) > 1 then 'validated found' end as found 
from activities

Thank you if you can help me on this one...

Upvotes: 1

Views: 44

Answers (3)

Cristian Caram Peñalver
Cristian Caram Peñalver

Reputation: 381

I found it simple to do writing the next query. I hope it help you.

SELECT user_id, 
       SUM(task_hour) 
  FROM activities 
 WHERE validated = 1 
    OR project_id NOT IN (SELECT project_id 
                            FROM activities 
                           WHERE validated = 1) 
 GROUP BY user_id;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

This would be very easy in standard SQL where you'd rank the records with ROW_NUMBER, but MySQL doesn't support this standard function. The ranking is simple: per user_id and project_id you want the better record. Better means validated true is preferred to false.

In MySQL true is 1 and false is 0. So you want the maximum validated per user_id and project_id. You can use an IN clause for this.

select user_id, sum(task_hour) as task_hour_total
from activities
where (user_id, project_id, validated) in
(
  select user_id, project_id, max(validated)
  from activities
  group by user_id, project_id
)
group by user_id;

Still a simple query. The difference to the ROW_NUMBER method is that records must be read twice.

Upvotes: 1

Richard
Richard

Reputation: 733

Ok, I found a way to do it. It is not very elegant but it works:

SELECT user_id,sum(task_hour) 
FROM 
(SELECT * FROM activities a1 WHERE a1.project_id NOT IN (SELECT project_id FROM activities as a2 WHERE validated = 1) 
UNION SELECT * FROM activities WHERE validated = 1) 
AS temp_table 
GROUP BY user_id

If anyone knows a better solution than this, don't hesitate otherwise,I will stay with this long and complex select.

Upvotes: 0

Related Questions