TAGS :Viewed: 8 - Published at: a few seconds ago

[ Eliminating extra step of creating temp table ]

I have a query that I execute with CREATE TEMPORARY TABLE AS SELECT because for the life of me I can't figure out how to write it as one query, hoping someone can show me how to eliminate the CREATE TEMPORARY TABLE AS.

My data:

monitor table:
date | ip | webid | userid | compid

SQL

CREATE TEMPORARY TABLE temp AS SELECT webid, userid, COUNT(ip) AS total, COUNT(DISTINCT ip) AS uniq FROM monitor GROUP BY webid, userid;

SELECT SUM(total), SUM(uniq) FROM temp;

The two sums for total/uniq after it's been grouped, are all I really need but as mentioned I'd like to eliminate the step of creating the table.

thanks in advance for any help.

Answer 1


You can use derived tables:

SELECT SUM(total), SUM(uniq) 
FROM (  SELECT webid, userid, COUNT(ip) AS total, COUNT(DISTINCT ip) AS uniq 
        FROM `monitor table`
        GROUP BY webid, userid) AS A

(I added a FROM on your definition of the temp table, since it was missing)