I'm a newbie to MySQL querying and need some assistance with the subqueries. I am using ASP .NET charting control that retrieves data from MySQL.I want to display a drill down chart and need some help on MySQL subquery.
Below is my table:
CREATE TABLE IF NOT EXISTS `data` ( `runtime` smallint(6) NOT NULL, `app` varchar(60) NOT NULL, `process` varchar(40) NOT NULL, `username` varchar(51) NOT NULL, `time` time NOT NULL, `date` date NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Step 1 : Showing a pie chart of Top 10 users with highest time between 2 dates.
I get the top 10 users used between 2 dates using the below query:
SELECT username ,SUM(runtime) as Runtime, process,ROUND(SUM(runtime/201600),2) as 'Total Time', role , date FROM data WHERE `date` BETWEEN 'date1' AND 'date2' Group BY process LIMIT 10.
Step 2: When user clicks on the individual user in chartArea, I wan to display the top 10 apps/process between specific dates.