how to query group by with group by?

Question

My table:

bucket  | bytes  | url   
-----
a       | 111     | url_1   
a       | 222     | url_1  
a       | 222     | url_2
a       | 222     | url_3
a       | 222     | url_4
a       | 222     | url_5
a       | 222     | url_6
a       | 222     | url_7
a       | 222     | url_8
a       | 222     | url_9
a       | 222     | url_10
a       | 222     | url_11
b       | 444     | url_4
b       | 555     | url_4
c       | 444     | url_5

I want to query:

group by bucket , like select bucket from table group by bucket .
for every one bucket , group by url , like select sum(bytes) from table where bucket = <every_one_bucket_in_the_group_by> group by url order by sum(bytes) desc limit 10 . for every one bucket , it has max 10 result rows.

How to combine this 2 sub-query ?

the result should like:

bucket   |   url   |  sum_bytes
----
a        | url_1   |  333
a        | url_2   |  222
a        | url_3   |  222
a        | url_4   |  222
a        | url_5   |  222
a        | url_6   |  222
a        | url_7   |  222
a        | url_8   |  222
a        | url_9   |  222
a        | url_10  |  222
b        | url_4   |  999
c        | url_5   |  444

share your final output sample – Zaynul Abadin Tuhin Jan 19 '21 at 06:55 — Zaynul Abadin Tuhin, Jan 19 '21 at 06:55

Zaynul Abadin Tuhin · Answer 1 · 2021-01-19T07:08:03.013

1

are you finding below one

select bucket,url,sum(bytes) as sumbyte from table 
group by bucket,url
order by sumbyte desc

edited Jan 19 '21 at 07:08

answered Jan 19 '21 at 06:57

Zaynul Abadin Tuhin

31,407
5
33
63

where bucket = – Rife Jan 19 '21 at 07:06
1

@Rife so share the actual condition and actually what you need.. how people read your mind – Zaynul Abadin Tuhin Jan 19 '21 at 07:07
thanks your answer. The sub-query has another condition: `limit 10`. – Rife Jan 19 '21 at 07:14

score 1 · Accepted Answer · answered Jan 19 '21 at 07:50

You need to use limit by-clause:

select bucket, url, sum(bytes) sum_bytes
from (
  /* emulate test dataset */
  select data.1 bucket, data.2 bytes, data.3 url
  from (
    select arrayJoin([
      ('a', 111, 'url_01'),   
      ('a', 222, 'url_01'),  
      ('a', 222, 'url_02'),
      ('a', 222, 'url_03'),
      ('a', 222, 'url_04'),
      ('a', 222, 'url_05'),
      ('a', 222, 'url_06'),
      ('a', 222, 'url_07'),
      ('a', 222, 'url_08'),
      ('a', 222, 'url_09'),
      ('a', 222, 'url_10'),
      ('a', 222, 'url_11'),
      ('b', 444, 'url_04'),
      ('b', 555, 'url_04'),
      ('c', 444, 'url_05')]) data)
  )
group by bucket, url
order by bucket, url
limit 10 by bucket

/*
┌─bucket─┬─url────┬─sum_bytes─┐
│ a      │ url_01 │       333 │
│ a      │ url_02 │       222 │
│ a      │ url_03 │       222 │
│ a      │ url_04 │       222 │
│ a      │ url_05 │       222 │
│ a      │ url_06 │       222 │
│ a      │ url_07 │       222 │
│ a      │ url_08 │       222 │
│ a      │ url_09 │       222 │
│ a      │ url_10 │       222 │
│ b      │ url_04 │       999 │
│ c      │ url_05 │       444 │
└────────┴────────┴───────────┘
*/

Thanks your answer, I want to order by sum_bytes after group by bucket and url , so I query like `select bucket, url, sum(bytes) as sum_bytes from table group by bucket, url order by sum_bytes limit 10 by bucket` . Is it right? — Rife, Jan 19 '21 at 08:37

how to query group by with group by?

2 Answers2