CODEWITHSUNDEEP
mysqlsql
einundswanzig
einundswanzig

Reputation: 314

MySQL intertwine two queries based on previous query result

I have a table with: id, client, domain and bytes. I have a query that gets the top 3 users either by client:

SELECT client FROM log
GROUP BY 1
ORDER BY SUM(bytes) DESC
LIMIT 3

or by domain 

SELECT domain FROM log
GROUP BY 1
ORDER BY SUM(bytes) DESC
LIMIT 3

Is there a way in MySQL to get this two mixed without a stored procedure? For example, get the top 3 clients, each client row followed by the top 3 domains used BY THAT CLIENT in a single query execution?

For example, to get the top 3 domain usage from client 192.168.1.1:

SELECT domain FROM log
WHERE client = '192.168.1.1'
GROUP BY 1
ORDER BY SUM(bytes) DESC
LIMIT 3

So for each row in query #1, somehow this query should be executed. So far I got to show a concatenated list of top domains for each client, sadly not related to the same client because I can't access outer_client in subquery:

SELECT client AS outer_client, top_domain
FROM log
JOIN (
   SELECT GROUP_CONCAT(t.domain) AS top_domain 
   FROM (
      SELECT domain 
      FROM log
      WHERE client = outer_client 
      GROUP BY 1 
      ORDER BY SUM(bytes)
      LIMIT 5
   ) t
) k
GROUP BY client
ORDER BY SUM(bytes)
LIMIT 5;

Upvotes: 1

Views: 251

Answers (4)

einundswanzig
einundswanzig

Reputation: 314

Here it is: 

select client,
substring_index(group_concat(domain order by sumbytes desc), ',', 5) as top5domains
from (
  select client, domain, sum(bytes) as sumbytes
  from log
  group by client, domain
) cd
group by client
order by sum(sumbytes) desc
limit 5;

Courtesy of @Gordon Linoff here: MySQL GROUP_CONCAT from subquery

Upvotes: 0

Joshua Huber
Joshua Huber

Reputation: 3533

One solution:

http://sqlfiddle.com/#!2/cc1b1/2/0

SELECT client
  , (SELECT domain
      FROM log
      WHERE client=top_clients.client
      GROUP BY client, domain
      ORDER BY SUM(bytes) DESC
      LIMIT 1) domain1
  , (SELECT domain
      FROM log
      WHERE client=top_clients.client
      GROUP BY client, domain
      ORDER BY SUM(bytes) DESC
      LIMIT 1 OFFSET 1) domain2
  , (SELECT domain
      FROM log
      WHERE client=top_clients.client
      GROUP BY client, domain
      ORDER BY SUM(bytes) DESC
      LIMIT 1 OFFSET 2) domain3
FROM (SELECT client FROM log GROUP BY client ORDER BY SUM(bytes) DESC LIMIT 3) top_clients;

My output:

+-------------+--------------+--------------+-------------+
| client      | domain1      | domain2      | domain3     |
+-------------+--------------+--------------+-------------+
| 192.168.1.1 | google.com   | facebook.com | twitter.com |
| 192.168.1.2 | facebook.com | twitter.com  | NULL        |
+-------------+--------------+--------------+-------------+

Upvotes: 2

Brian Mego
Brian Mego

Reputation: 1469

According to your question, you are ordering by the same thing in each example. If that's the case, the row you return should have both client and domain already in it. So would this solve your problem?

SELECT client, domain FROM log
GROUP BY 1
ORDER BY SUM(bytes) DESC
LIMIT 3

Upvotes: -1

durbnpoisn
durbnpoisn

Reputation: 4669

Assuming you have access to PHPMySQL, and your server allows it, what you're looking for is a VIEW.

You write your SQL to get your data set. And rather than saving it in a temporary table (which would make it static), you save it in a VIEW. Once you have that view, you can always use it the same as if it were a table. In fact, it actually SHOWS the data set in a table format as if that's exactly what it was.
You can run queries against it, with JOINS... Anything you would do with a table. And it's actual live data.

Upvotes: 0

Related Questions