title: combine multiple group by queries into single query
body: <p>I have a table similar in structure to the one below, I have four grouping variables (Region,Site,Method,Client) and three metrics that each have an accompanying "bucket" column that gives the quintile of the metric.  I would like to perform some aggregation by the grouping variables and the quintile bucket.  Right now I have to run three separate queries and attempt to join the results which is leading to duplicates.  Is there a way to combine the three queries  below into one? </p>
  
  <pre><code>Select Region,Site,Method,Client,A_bucket,
  count(ID) as nA,
  avg(Metric_A) as Avg.A,
  min(Metric_A) as Min.A
  From S_overflow
  Group By Region,Site,Method,Client,A_bucket
  </code></pre>
  
  <p>and</p>
  
  <pre><code>Select Region,Site,Method,Client,B_bucket,
  count(ID) as nB,
  avg(Metric_B) Avg.B,
  min(Metric_B) Min.B
  From S_overflow
  Group By Region,Site,Method,Client,B_bucket
  </code></pre>
  
  <p>and</p>
  
  <pre><code>Select Region,Site,Method,Client,C_bucket,
  count(ID) as nC,
  avg(Metric_C) as avg.C,
  min(Metric_C) as Min.C
  From S_overflow
  Group By Region,Site,Method,Client,C_bucket
  </code></pre>
  
  <p>Here are the desired results which I was able to get in R by ordering each of the results by Region,Site,Method,Client,bucket and binding columns, however I need a HQL (SQL) solution and would preferably like to reduce the three queries to one.</p>
  
  <p><strong>Desired Results</strong> </p>
  
  <pre><code>+--------+---------------+--------+--------+-----------+----+-------------+-------+-----------+----+-------------+-------+-----------+----+-------------+-------+
  | Region |     Site      | Method | Client | A_buckets | nA |    Avg.A    | min.A | B_buckets | nB |    Avg.B    | min.B | C_buckets | nC |    avg.C    | min.C |
  +--------+---------------+--------+--------+-----------+----+-------------+-------+-----------+----+-------------+-------+-----------+----+-------------+-------+
  | A      | Local         |      1 | mno    |         1 |  2 |        73.5 |    22 |         1 |  2 |         153 |    49 |         1 |  2 |        80.5 |    49 |
  | A      | Local         |      1 | mno    |         2 |  2 |       343.5 |   302 |         2 |  2 |         420 |   363 |         2 |  2 |         395 |   355 |
  | A      | Local         |      1 | mno    |         3 |  2 |       495.5 |   415 |         3 |  2 |       682.5 |   631 |         3 |  2 |       564.5 |   485 |
  | A      | Local         |      1 | mno    |         4 |  2 |       585.5 |   585 |         4 |  2 |         795 |   762 |         4 |  2 |       696.5 |   677 |
  | A      | Local         |      1 | mno    |         5 |  2 |         751 |   600 |         5 |  2 |       946.5 |   908 |         5 |  2 |         848 |   717 |
  | A      | Local         |      1 | rst    |         1 |  2 |        24.5 |    21 |         1 |  2 |       241.5 |   203 |         1 |  2 |       156.5 |    97 |
  | A      | Local         |      1 | rst    |         2 |  2 |       232.5 |   193 |         2 |  2 |       360.5 |   316 |         2 |  2 |         280 |   234 |
  | A      | Local         |      1 | rst    |         3 |  2 |       421.5 |   377 |         3 |  2 |       465.5 |   438 |         3 |  2 |         374 |   331 |
  | A      | Local         |      1 | rst    |         4 |  2 |       647.5 |   602 |         4 |  2 |       690.5 |   687 |         4 |  2 |         542 |   466 |
  | A      | Local         |      1 | rst    |         5 |  2 |       937.5 |   937 |         5 |  2 |       956.5 |   924 |         5 |  2 |         797 |   691 |
  | A      | Local         |      1 | xyz    |         1 |  2 |         209 |    70 |         1 |  2 |         224 |    80 |         1 |  2 |       159.5 |   148 |
  | A      | Local         |      1 | xyz    |         2 |  2 |         477 |   382 |         2 |  2 |         425 |   410 |         2 |  2 |       232.5 |   205 |
  | A      | Local         |      1 | xyz    |         3 |  2 |       611.5 |   596 |         3 |  2 |         521 |   444 |         3 |  2 |       315.5 |   287 |
  | A      | Local         |      1 | xyz    |         4 |  2 |         677 |   634 |         4 |  2 |       726.5 |   628 |         4 |  2 |         375 |   367 |
  | A      | Local         |      1 | xyz    |         5 |  2 |       829.5 |   807 |         5 |  2 |         843 |   838 |         5 |  2 |       671.5 |   570 |
  | B      | International |      2 | mno    |         1 |  3 | 175.6666667 |    15 |         1 |  3 |         114 |    58 |         1 |  3 |         202 |   159 |
  | B      | International |      2 | mno    |         2 |  2 |       515.5 |   511 |         2 |  2 |         298 |   245 |         2 |  2 |         394 |   370 |
  | B      | International |      2 | mno    |         3 |  3 | 614.3333333 |   573 |         3 |  3 |         569 |   508 |         3 |  3 | 493.3333333 |   430 |
  | B      | International |      2 | mno    |         4 |  2 |       715.5 |   708 |         4 |  2 |       786.5 |   686 |         4 |  2 |         737 |   627 |
  | B      | International |      2 | mno    |         5 |  2 |         814 |   760 |         5 |  2 |         941 |   901 |         5 |  2 |       861.5 |   847 |
  | B      | International |      2 | rst    |         1 |  3 |          77 |    26 |         1 |  3 | 133.3333333 |     3 |         1 |  3 |          43 |    12 |
  | B      | International |      2 | rst    |         2 |  2 |         329 |   225 |         2 |  2 |       262.5 |   243 |         2 |  2 |         259 |   192 |
  | B      | International |      2 | rst    |         3 |  3 | 522.6666667 |   450 |         3 |  3 |         505 |   380 |         3 |  3 | 465.3333333 |   336 |
  | B      | International |      2 | rst    |         4 |  2 |         701 |   578 |         4 |  2 |       713.5 |   673 |         4 |  2 |         790 |   755 |
  | B      | International |      2 | rst    |         5 |  2 |         948 |   904 |         5 |  2 |       929.5 |   898 |         5 |  2 |       846.5 |   834 |
  | B      | International |      2 | xyz    |         1 |  3 | 119.6666667 |   101 |         1 |  3 | 170.3333333 |   125 |         1 |  3 | 259.3333333 |    43 |
  | B      | International |      2 | xyz    |         2 |  3 |         373 |   282 |         2 |  3 | 279.3333333 |   207 |         2 |  3 | 695.3333333 |   601 |
  | B      | International |      2 | xyz    |         3 |  2 |         493 |   492 |         3 |  2 |         476 |   462 |         3 |  2 |         805 |   804 |
  | B      | International |      2 | xyz    |         4 |  3 | 676.3333333 |   552 |         4 |  3 | 554.3333333 |   511 |         4 |  3 |         850 |   820 |
  | B      | International |      2 | xyz    |         5 |  2 |         896 |   868 |         5 |  2 |       770.5 |   722 |         5 |  2 |       929.5 |   906 |
  +--------+---------------+--------+--------+-----------+----+-------------+-------+-----------+----+-------------+-------+-----------+----+-------------+-------+
  </code></pre>
  
  <p><strong>Original Data</strong></p>
  
  <pre><code> +--------+---------------+--------+--------+----+----------+-----------+----------+-----------+----------+-----------+
  | Region |     Site      | Method | Client | ID | Metric_A | A_buckets | Metric_B | B_buckets | Metric_C | C_buckets |
  +--------+---------------+--------+--------+----+----------+-----------+----------+-----------+----------+-----------+
  | A      | Local         |      1 | mno    |  3 |      125 |         1 |      257 |         1 |      677 |         4 |
  | A      | Local         |      1 | mno    |  6 |      585 |         4 |      762 |         4 |      644 |         3 |
  | A      | Local         |      1 | mno    |  9 |       22 |         1 |      631 |         3 |      717 |         5 |
  | A      | Local         |      1 | mno    | 12 |      576 |         3 |      363 |         2 |      112 |         1 |
  | A      | Local         |      1 | mno    | 15 |      586 |         4 |      985 |         5 |      435 |         2 |
  | A      | Local         |      1 | mno    | 18 |      902 |         5 |      828 |         4 |      716 |         4 |
  | A      | Local         |      1 | mno    | 21 |      302 |         2 |       49 |         1 |      355 |         2 |
  | A      | Local         |      1 | mno    | 24 |      600 |         5 |      477 |         2 |      485 |         3 |
  | A      | Local         |      1 | mno    | 27 |      415 |         3 |      908 |         5 |      979 |         5 |
  | A      | Local         |      1 | mno    | 30 |      385 |         2 |      734 |         3 |       49 |         1 |
  | A      | Local         |      1 | rst    |  2 |       21 |         1 |      203 |         1 |      234 |         2 |
  | A      | Local         |      1 | rst    |  5 |      937 |         5 |      438 |         3 |      618 |         4 |
  | A      | Local         |      1 | rst    |  8 |       28 |         1 |      924 |         5 |       97 |         1 |
  | A      | Local         |      1 | rst    | 11 |      377 |         3 |      989 |         5 |      326 |         2 |
  | A      | Local         |      1 | rst    | 14 |      602 |         4 |      687 |         4 |      216 |         1 |
  | A      | Local         |      1 | rst    | 17 |      693 |         4 |      405 |         2 |      903 |         5 |
  | A      | Local         |      1 | rst    | 20 |      938 |         5 |      316 |         2 |      466 |         4 |
  | A      | Local         |      1 | rst    | 23 |      193 |         2 |      280 |         1 |      331 |         3 |
  | A      | Local         |      1 | rst    | 26 |      272 |         2 |      694 |         4 |      691 |         5 |
  | A      | Local         |      1 | rst    | 29 |      466 |         3 |      493 |         3 |      417 |         3 |
  | A      | Local         |      1 | xyz    |  1 |      572 |         2 |      848 |         5 |      148 |         1 |
  | A      | Local         |      1 | xyz    |  4 |      627 |         3 |      628 |         4 |      287 |         3 |
  | A      | Local         |      1 | xyz    |  7 |      596 |         3 |      825 |         4 |      383 |         4 |
  | A      | Local         |      1 | xyz    | 10 |      807 |         5 |      410 |         2 |      367 |         4 |
  | A      | Local         |      1 | xyz    | 13 |      382 |         2 |      368 |         1 |      260 |         2 |
  | A      | Local         |      1 | xyz    | 16 |      852 |         5 |      598 |         3 |      570 |         5 |
  | A      | Local         |      1 | xyz    | 19 |      634 |         4 |      444 |         3 |      773 |         5 |
  | A      | Local         |      1 | xyz    | 22 |      720 |         4 |       80 |         1 |      344 |         3 |
  | A      | Local         |      1 | xyz    | 25 |      348 |         1 |      838 |         5 |      205 |         2 |
  | A      | Local         |      1 | xyz    | 28 |       70 |         1 |      440 |         2 |      171 |         1 |
  | B      | International |      2 | mno    | 33 |      723 |         4 |      508 |         3 |      418 |         2 |
  | B      | International |      2 | mno    | 36 |      868 |         5 |      686 |         4 |      159 |         1 |
  | B      | International |      2 | mno    | 39 |      352 |         1 |      196 |         1 |      208 |         1 |
  | B      | International |      2 | mno    | 42 |      520 |         2 |      887 |         4 |      847 |         4 |
  | B      | International |      2 | mno    | 45 |      708 |         4 |       88 |         1 |      876 |         5 |
  | B      | International |      2 | mno    | 48 |      511 |         2 |      351 |         2 |      370 |         2 |
  | B      | International |      2 | mno    | 51 |      160 |         1 |      607 |         3 |      847 |         5 |
  | B      | International |      2 | mno    | 54 |      577 |         3 |      245 |         2 |      239 |         1 |
  | B      | International |      2 | mno    | 57 |      693 |         3 |      901 |         5 |      430 |         3 |
  | B      | International |      2 | mno    | 60 |      760 |         5 |      592 |         3 |      627 |         4 |
  | B      | International |      2 | mno    | 63 |      573 |         3 |       58 |         1 |      571 |         3 |
  | B      | International |      2 | mno    | 66 |       15 |         1 |      981 |         5 |      479 |         3 |
  | B      | International |      2 | rst    | 32 |      992 |         5 |      898 |         5 |      834 |         5 |
  | B      | International |      2 | rst    | 35 |      578 |         4 |      754 |         4 |      336 |         3 |
  | B      | International |      2 | rst    | 38 |      555 |         3 |      623 |         3 |       67 |         1 |
  | B      | International |      2 | rst    | 41 |      824 |         4 |        3 |         1 |      755 |         4 |
  | B      | International |      2 | rst    | 44 |       77 |         1 |      961 |         5 |      825 |         4 |
  | B      | International |      2 | rst    | 47 |       26 |         1 |      380 |         3 |      412 |         3 |
  | B      | International |      2 | rst    | 50 |      904 |         5 |      203 |         1 |       12 |         1 |
  | B      | International |      2 | rst    | 53 |      433 |         2 |      282 |         2 |       50 |         1 |
  | B      | International |      2 | rst    | 56 |      128 |         1 |      512 |         3 |      859 |         5 |
  | B      | International |      2 | rst    | 59 |      563 |         3 |      194 |         1 |      326 |         2 |
  | B      | International |      2 | rst    | 62 |      225 |         2 |      673 |         4 |      192 |         2 |
  | B      | International |      2 | rst    | 65 |      450 |         3 |      243 |         2 |      648 |         3 |
  | B      | International |      2 | xyz    | 31 |      101 |         1 |      185 |         1 |      804 |         3 |
  | B      | International |      2 | xyz    | 34 |      127 |         1 |      125 |         1 |      701 |         2 |
  | B      | International |      2 | xyz    | 37 |      492 |         3 |      462 |         3 |      601 |         2 |
  | B      | International |      2 | xyz    | 40 |      924 |         5 |      612 |         4 |      906 |         5 |
  | B      | International |      2 | xyz    | 43 |      786 |         4 |      722 |         5 |      784 |         2 |
  | B      | International |      2 | xyz    | 46 |      442 |         2 |      511 |         4 |       43 |         1 |
  | B      | International |      2 | xyz    | 49 |      552 |         4 |      207 |         2 |      820 |         4 |
  | B      | International |      2 | xyz    | 52 |      868 |         5 |      819 |         5 |      953 |         5 |
  | B      | International |      2 | xyz    | 55 |      494 |         3 |      540 |         4 |      855 |         4 |
  | B      | International |      2 | xyz    | 58 |      131 |         1 |      267 |         2 |      875 |         4 |
  | B      | International |      2 | xyz    | 61 |      282 |         2 |      201 |         1 |      806 |         3 |
  | B      | International |      2 | xyz    | 64 |      395 |         2 |      364 |         2 |      156 |         1 |
  | B      | International |      2 | xyz    | 67 |      691 |         4 |      490 |         3 |      579 |         1 |
  +--------+---------------+--------+--------+----+----------+-----------+----------+-----------+----------+-----------+
  </code></pre>