# Shantanu's Blog

Corporate Consultant

## MySQL Case Study - 117

Calculate Percentage:

I'm very new to MySQL and have a query I'm trying to achieve, if anyone can point me in the right direction, I would greatly appreciate it. I have a table created to store print job logs from a print server:

Table = printLogs

logNum mediumint(8)
logDateTime datetime
docNum tinyint(3)
docName varchar(200)
owner varchar(25)
printerName varchar(25)
printerPort varchar(50)
sizeInBytes varchar(50)
pagesPrinted smallint(10)

What I'm trying to do is find a query that, based on a 'printerName', will display 'owner' and percentage derived from the total number of pages printed to that 'printerName' for a given date range.

Something like this:

+--------+--------------------------+
| owner | Percentage of total jobs |
+--------+--------------------------+
| Mary | 50% |
| John | 30% |
| Kim | 10% |
| Sue | 10% |
+--------+--------------------------+

Each row of the table is a different print job and can be one of about 20 different owners who have printed any number of pages to any of 10 printers. The main reason for the query is to be to determine which group/owner uses each printer the most.

Thanks for ANY suggestions,

SELECT a.owner, a.printerName, a.logDateTime, (
sum( a.pagesPrinted ) / b.hojas) *100 AS percent
FROM printlogs a
LEFT JOIN (
SELECT logDateTime, printerName, sum( pagesPrinted ) AS hojas
FROM printlogs
GROUP BY logDateTime, printerName
) AS b ON b.logDateTime = a.logDateTime
AND b.printerName = a.printerName
WHERE a.logDateTime > '2006-03-01'
AND a.logDateTime > '2006-04-01'
AND a.printerName = 'MO161_4_HP4600'
GROUP BY a.owner, a.printerName, a.logDateTime