StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POavoiding creation of temporary or permanent tables
text
Body
copied!<p>I want to manipulate with a table, but the only solution I found so far is to create some tables first and join them together to the desired results. </p> <p>I'm trying to avoid creating tables and dropping them at the end of my MySQL query which btw, I'm running on phpmyadmin page. </p> <p>Here is the data: I have one table containing user_id, columnA_unixtime, columnB_unixtime -- meaning that for each user there are two unix_time stored in the database for two different events. </p> <pre> user_id eventA_join eventB_join 1 1321652009 1321652009 2 0 1321652257 3 0 1321668650 4 1321669261 0 </pre> <p>what I want to have is a table showing how many users joined the two events for each day. Something like this (just a sample)</p> <pre> day eventA eventB 11/18/11 3 2 11/19/11 11 8 11/20/11 6 3 11/21/11 17 11 </pre> <p>Here is the code I'm using so far:</p> <pre><code>CREATE TABLE table1( day VARCHAR(256), eventA_count INT); INSERT INTO table1 (day, eventA_count) (SELECT DATE(FROM_UNIXTIME('eventA_join') ) AS 'day', COUNT('user_id') AS 'eventA_count' FROM org_table WHERE 'eventA_join' > 0 GROUP BY day); CREATE TABLE table2( day VARCHAR(256), eventB_count INT); INSERT INTO table2 (day, eventB_count) (SELECT DATE(FROM_UNIXTIME('eventB_join') ) AS 'day', COUNT('user_id') AS 'eventB_count' FROM org_table WHERE 'eventB_join' > 0 GROUP BY day); SELECT t.day, t1.eventA_count, t2.eventB_count FROM (SELECT DISTINCT day FROM table1 UNION SELECT DISTINCT day FROM table2) t LEFT JOIN table1 t1 ON t.day = t1.day LEFT JOIN table2 t2 ON t.day = t2.day DROP table2; DROP table1; </code></pre> <p>As far as I tried I couldn't use table variables in phpmyadmin and neither I could use template tables because there was no way to refer to template tables multiple times (error #10327 Can't reopen temporary table) when I try to join them together. </p> <p>Is there anyway I avoid creating tables but gain what I'm looking for? Any thoughts? </p> <p>Edit: both tables are getting data from 'org_table' which is now corrected in the code. </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