No MySQL tenho tabelas, simplificadas da seguinte forma:
companies (id, name, rank, trade_sector_id)
orders (id, company_id, paid, subtotal)
trade_sectors (id, label)
companies_notes (id, company_id, notes)
Quero uma única linha para cada empresa contendo:
- Nome da companhia
- Número de encomendas
- Total de todos os subtotais
- Notas da empresa
Para simplificar aqui estou selecionando apenas uma empresa, com id=14401. Possui 68 pedidos:
SELECT
companies.id AS company_id,
companies.account_name,
COUNT(orders.id) AS numSales,
SUM(orders.`subtotal`) AS subtotal,
MAX(trade_sectors.label) AS trade_sector,
MAX(companies_notes.`notes`) AS notes
FROM companies
LEFT JOIN `orders` ON (companies.id = orders.`company_id` AND orders.`paid` = 1)
LEFT JOIN `trade_sectors` ON (companies.trade_sector_id = trade_sectors.`id`)
LEFT JOIN `companies_notes` ON (`companies_notes`.`company_id` = companies.id)
WHERE companies.id = '14401'
GROUP BY companies.id
ORDER BY companies.rank DESC;
O problema
Existem 68 pedidos para esta empresa, mas estou obtendo numSales como 136 (ou seja, 2x o número) e também o subtotal é 2x maior do que deveria ser.
Mas se eu remover a junção do NOTES, está correto:
SELECT
companies.id AS company_id,
companies.account_name,
COUNT(orders.id) AS numSales,
SUM(orders.`subtotal`) AS subtotal,
MAX(trade_sectors.label) AS trade_sector
FROM companies
LEFT JOIN `orders` ON (companies.id = orders.`company_id` AND orders.`paid` = 1)
LEFT JOIN `trade_sectors` ON (companies.trade_sector_id = trade_sectors.`id`)
WHERE companies.id = '14401'
GROUP BY companies.id
ORDER BY companies.rank DESC;
Parece que a junção de notas está me dando 2 linhas por pedido. Sim, EXISTEM duas linhas de notas para esta empresa (deve haver apenas 1), mas isso não é aplicado tecnicamente. Eu pensei que usando a função de agregação MAX no companies_notes. notesapenas um seria considerado. Na verdade, a cláusula Group BY exige que as colunas sejam agregadas.
Como posso evitar que a junção crie registros duplicados que afetam os valores SUM()e MAX()?