StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POComplex SQL query optimization suggestions please
text
Body
copied!<p><code>enter code here</code>I have a table with structure like this:</p> <pre><code>| ID (bigint) | APPID (Bigint)| USAGE_START_TIME (datetime) | SESSION_TIME (bigint) | USERID (bigint) | ----------------------------------------------------------------------------------------------------------- | 1 | 1 | 2013-05-03 04:42:55 | 400 | 12 | | 2 | 1 | 2013-05-12 06:22:45 | 200 | 12 | | 3 | 2 | 2013-06-12 08:44:24 | 350 | 12 | | 4 | 2 | 2013-06-24 04:20:56 | 2 | 12 | | 5 | 3 | 2013-06-26 08:20:26 | 4 | 12 | | 6 | 4 | 2013-09-12 05:48:27 | 50 | 12 | </code></pre> <p>Now if put is userid, i want to get the total (sum of) session_time for each month (of all app), month wise for the last 6 months.</p> <p>If an app is used more than once in a month (based on usage_start_time), only the latest session_time should included in the sum for that app.</p> <p>For the above example, the result should be like this (if present month is october, and input is userid=13):</p> <pre><code>| MONTH | TOTAL_SESSION_TIME | ------------------------------------ | 10 | 0 | | 9 | 50 | | 8 | 0 | | 7 | 0 | | 6 | 6 | | 5 | 200 | </code></pre> <p>Here the month represents month in numeric form (for ex: 10 represents october).</p> <p>Presently i'm using <strong>separate queries for each month</strong>. for ex:</p> <pre><code>SELECT COALESCE(SUM(failcount),0) AS TOTAL_SESSION_TIME , MONTH(CURRENT_DATE) AS MONTH FROM appstime WHERE MONTH(`USAGE_START_TIME`) = MONTH(CURRENT_DATE) AND userid=12 </code></pre> <p>But this doesn't give me expected results.</p> <p>Also, i want to know if i can do this with a single query instead of query for each month. I'm using PHP+Mysql.</p> <p><strong>Please check the sql fiddle here</strong>: <a href="http://sqlfiddle.com/#!2/4eaf2" rel="nofollow">http://sqlfiddle.com/#!2/4eaf2</a></p> <p>Thank you,<br/> Sash</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload