Skip to content
Advertisement

SQL: How to divide column by counts?

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

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement