CODEWITHSUNDEEP
mysqljoincountsumaggregate
davidahines
davidahines

Reputation: 4094

Aggregate functions with joins

So I'm trying to count the number of parts, number of tasks, the quantity in each job and the time that it took to manufacture each job but I'm getting some funky results. If I run this:

SELECT
  j.id, 
    mf.special_instructions,
  count(p.id) as number_of_parts,
  count(t.id) as number_of_tasks,
  j.quantity as job_quantity

FROM
  sugarcrm2.mf_job mf
INNER JOIN ramses.jobs j on
  mf.id = j.mf_job_id
INNER JOIN ramses.parts p on
  j.id = p.job_id
INNER JOIN ramses.tasks t on
  p.id = t.part_id
INNER JOIN ramses.batch_log l on
  t.batch_id = l.batch_id

WHERE 
  mf.job_description                LIKE "%BACKBLAZE%" OR
  mf.customer_name                  LIKE "%BACKBLAZE%" OR
  mf.customer_ref                   LIKE "%BACKBLAZE%" OR
  mf.technical_company_name LIKE "%BACKBLAZE%" OR
  mf.description                        LIKE "%BACKBLAZE%" OR
  mf.name                                   LIKE "%BACKBLAZE%" OR
  mf.enclosure_style                LIKE "%BACKBLAZE%" OR 
    mf.special_instructions     LIKE "%BACKBLAZE%"
Group by j.id

Then I get this for one of the records:

"id";"special_instructions";"number_of_parts";"number_of_tasks";"job_quantity"
"10cfa05c-a8c0-b1e6-2a36-4e52579c82ab";"BACKBLAZE TEMPLATE full assembled/tested RAL 5017 custom Logo - cutout";"105";"105";"1"

Meaning there should be 105 tasks and 105 parts, this is unusual as a part normally has 8 to 10 tasks and also a job should normally only have maybe 10 parts in each job. When I run this:

SELECT * from ramses.parts where job_id = "10cfa05c-a8c0-b1e6-2a36-4e52579c82ab"

Only 13 rows are returned. Is there some kind of problem with the way I'm joining things?

Upvotes: 0

Views: 121

Answers (1)

GolezTrol
GolezTrol

Reputation: 116190

Use count(distinct p.id) to count the number of different parts. Now you're just counting the total number of rows.

Upvotes: 1

Related Questions