So I have this query
SELECT distinct toner.device_id, printer.paper_count from toner join printer on printer.device_id = toner.device_id;
This will print something like
device_id | paper_count |
---|---|
1 | 5000 |
2 | 10000 |
And I have this other other query that will return the number of times the toner has been changed according to some date. I used count to count the number of times it’s been changed.
select device_id, count(toner_color) as 'Change Count' from toner where toner_color = 'Black' group by device_id;
This will print something like
device_id | Change Count |
---|---|
1 | 2 |
2 | 2 |
What I’m trying to get is the average amount of papers printed by 1 ink cannister e.g. 5000 paper / 2 black ink cannisters = 2500.
So I’m looking to get something like this
device_id | paper_count | Change Count | Average |
---|---|---|---|
1 | 5000 | 2 | 2500 |
2 | 10000 | 2 | 5000 |
Advertisement
Answer
would group by p.device_id, p.paper_count
, while filtering by where toner_color = 'Black'
:
select p.device_id, p.paper_count, count(toner_color) as 'Change Count' , round(p.paper_count / count(toner_color)) as 'Average' from toner t join printer p on p.device_id = t.device_id where toner_color = 'Black' group by p.device_id, p.paper_count;
MySQL v.8.0 on DB Fiddle