SQL: Group by multiple columns

Question

I created a table similar to this by creating a couple of temporary tables and joining them in my database:

customerid  Segment Sales   Country
1           1       $3      US
2           2       $4      CAN
3           3       $5      US 
4           5       $6      CAN
5           4       $5      US
6           6       $1      CAN
7           7       $4      US
8           3       $2      CAN
9           2       $2      US
10          1       $1      CAN
11          9       $5      US
12          10      $2      CAN
13          1       $4      US
14          3       $2      CAN
15          4       $1      US

What I want to achieve is this:

Country Segment Count of customer by segment    sum of sales by segment
CAN     1       70                              $3,000 
CAN     2       30                              $3,000 
CAN     3       20                              $3,000 
CAN     4       110                             $3,000 
CAN     5       90                              $3,000 
CAN     6       10                              $3,000 
CAN     7       60                              $3,000 
CAN     8       5000                            $3,000 
CAN     9       2527.857143                     $3,000 
CAN     10      2939.880952                     $3,000 
US      1       3351.904762                     $3,000 
US      2       3763.928571                     $3,000 
US      3       4175.952381                     $3,000 
US      4       4587.97619                      $3,000 
US      5       5000                            $3,000 
US      6       5412.02381                      $3,000 
US      7       5824.047619                     $3,000 
US      8       6236.071429                     $3,000 
US      9       6648.095238                     $3,000 
US      10      7060.119048                     $3,000

How do I achieve this?

. . It is much better to put the tables in the question as text than as links. — Gordon Linoff, Jun 01 '16 at 01:36
The tables are not showing up nice here. The segments are 1-10. — Olivier De Ridder, Jun 01 '16 at 15:27

score 0 · Answer 1 · answered Jun 01 '16 at 01:39

A partition would be useful if you wanted to retain all the records in the original table while also showing some aggregates. However, from what you showed us it looks like you want a result set where each Country/Segment appears once. In this case, a simple GROUP BY should work:

SELECT Country, Segment, COUNT(*) AS `Count of customer by segment`,
    SUM(Sales) AS `sum of sales by segment`
FROM yourTable
GROUP BY Country, Segment
ORDER BY Country, Segment

SQL: Group by multiple columns

1 Answers1