COUNT and SUM with 3 tables

Asked

Viewed 135 times

0

I’m making an IT management system. I have 3 tables: Computers, offices, office_keys

inserir a descrição da imagem aqui

Example:

offices
______________________________________
id      | 1
version | Home & Business 2016


office_keys
_______________________________________
id        | 1
office_id | 1
key       | AAAA-BBBB-CCCC-DDDD-EEEE


computers
_______________________________________
id            | 1
name          | DESKTOP
office_key_id | 1

The field Quantity table office_keys represents the number of times that key can be used on computers (office standard).

I want to do a query that brings all versions of office, the sum of the amount of Keys (Quantity) and sum of how many keys are being used (only the sum).

For example:

If I have 4 office keys 2016 and 2 computers using 2 of those keys, or if I have 3 office 2013 keys and 3 computers using those 3 keys, I want to receive:

Office   |    Quantidade   |   Em uso
2016     |    4            |   2
2013     |    3            |   3

I tried to do that: 

SELECT DISTINCT
offices.id, offices.version,
SUM(office_keys.quantity) AS quantity,
COUNT(computers.id) AS in_use
FROM
offices
LEFT JOIN office_keys ON office_keys.office_id = offices.id
LEFT JOIN computers ON computers.office_key_id = office_keys.id
GROUP BY
offices.id

But the sums are not right because of the duplicate lines.

How can I fix this? Thank you!

mysql sql

  • Either remove the field offices.version from the select-list or add this field in the GROUP BY clause (GROUP BY offices.id, offices.version).

  • I’ve tried that, but it doesn’t solve it... the sum of the amount keeps going wrong because of the duplicate records. I want to do the sum of quantities and Count of computers grouped by office.

  • Have you assessed whether the use of the DISTINCT clause in the aggregation function meets your needs?

1 answer

0

I managed to solve!

I just needed to do a subquery inside JOIN:

SELECT 
offices.id,
offices.version,
SUM(sub.quantity) as quantity,
SUM(in_use)
FROM offices
JOIN 
(
    SELECT 
        office_keys.office_id,
        office_keys.quantity,
        COUNT(computers.id) as in_use
    FROM
        office_keys
    LEFT JOIN computers ON computers.office_key_id = office_keys.id
    GROUP BY office_keys.id
) as sub 
ON sub.office_id = offices.id
GROUP BY sub.office_id

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.