Need help creating SQL query

Question

I have thus two tables:

 CREATE TABLE `workers` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `number` int(7) NOT NULL,
  `percent` int(3) NOT NULL,
  `order` int(7) NOT NULL,
      PRIMARY KEY (`id`)
 );
 CREATE `data` (
  `id` bigint(15) NOT NULL AUTO_INCREMENT,
  `workerId` int(7) NOT NULL,
  PRIMARY KEY (`id`)
 );

I want to return the first worker (order by order ASC) that his number of rows in the table data times percent(from table workers) /100 is smaller than number(from table workers.

I have tried this query:

SELECT workers.id, COUNT(data.id) AS `countOfData`
FROM `workers` as workers, `data` as data
WHERE data.workerId = workers.id
   AND workers.percent * `countOfData` < workers.number
LIMIT 1

But I get the error:

#1054 - Unknown column 'countOfData' in 'where clause'

Erm, if this is not mysql, please tag it with the appropriate rdbms. — Nick Rolando
– Nick Rolando, Commented Jun 12, 2012 at 21:32
You should not have order as a column name as it is a keyword in most of the SQL's. — JHS
– JHS, Commented Jun 12, 2012 at 21:33
it is mysql.... Juniad - ignore the name - let's call it sortOrder - — Nir
– Nir, Commented Jun 12, 2012 at 21:34
What have you tried so far? If you have attempted the query yourself, it is always good to show your current thoughts/effort. People are more likely to assist you if they can see you've put forth effort and have made an attempt. — Adam Wenger
– Adam Wenger, Commented Jun 12, 2012 at 21:35

Lamak · Accepted Answer · 2012-06-12 21:47:28Z

1

This should work:

SELECT A.id
FROM workers A
LEFT JOIN (SELECT workerId, COUNT(*) AS Quant
            FROM data
            GROUP BY workerId) B
ON A.id = B.workerId
WHERE (COALESCE(Quant,0) * `percent`)/100 < `number`
ORDER BY `order`
LIMIT 1

edited Jun 12, 2012 at 21:47

answered Jun 12, 2012 at 21:39

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

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

3 Comments

Nir Over a year ago

Thanks - it's work ok.. but if the worker in order 1 have no records on the data table, the query should retuen him. But it returned empty result

Lamak Over a year ago

@Nir - I updated my answer to take your issue into account. I also deleted the DESC on the ORDER clause

Nir Over a year ago

By the way, i had a little mistake.. I whanted it to be WHERE COALESCE(Quant,0) * number < (percent)/100

Andomar · Accepted Answer · 2012-06-12 21:47:22Z

0

You could calculate the number of rows per worker in a subquery. The subquery can be joined to the worker table. If you use a left join, a worker with no data rows will be considered:

select  *
from    workers w
left join    
        (
        select  workerId
        ,       count(*) as cnt
        from    data
        group by
                workerId
        ) d
on      w.id = d.workerId
where   coalesce(d.cnt, 0) * w.percent / 100 < w.number
order by
        w.order
limit   1

edited Jun 12, 2012 at 21:47

answered Jun 12, 2012 at 21:40

Andomar

239k55 gold badges387 silver badges412 bronze badges

2 Comments

Nir Over a year ago

Thanks - it's work ok.. but if the worker in order 1 have no records on the data table, the query should retuen him. But it returned empty result

Andomar Over a year ago

Updated the answer so it should return a worker with no records in the data table

Collectives™ on Stack Overflow

Need help creating SQL query

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related