Group the sum of sales of different customer age groups (SQL)-CodePudding

hoping someone can help me as I continue to learn sql.

I have a data set that contains sales of different customers as well as their respective ages.

My goal is to group together customer ages in intervals of 10 (40s, 50s, 60s, 70s, etc.) then show the sum of sales of each of those age groups.

Here's what I was able to do so far

I'm struggling with grouping together the age group and sales. Ideally, the result above would show an age range of 40-49 and the then entire sum of those sales.

Using Microsoft SQL Server 2019

Thanks in advance for any help, really appreciated.

CodePudding user response：

something like this -

SELECT 
    case when cast("Customer Age" as int) between 40 and 49 then '40s' 
        when cast("Customer Age" as int) between 50 and 59 then '50s'
        when cast("Customer Age" as int) between 60 and 69 then '60s'
            end as age_group
    ,sum(Sales) AS Revenue
FROM dbo.customerdata
GROUP BY 
    case when cast("Customer Age" as int) between 40 and 49 then '40s' 
        when cast("Customer Age" as int) between 50 and 59 then '50s'
        when cast("Customer Age" as int) between 60 and 69 then '60s'
            end

CodePudding user response：

Imagining you have a 'customer' table and 'sales' table with the data...

customers data:

id  name           age
1   Tim          25
2   Mike         43
3   Gervase      37
4   Karthik      42
5   Tyler        58
6   Lucas        29
7   Amy          32
8   John         36
9   Tathiana     28
10  Charles      52
11  Milenka      24
12  Dave         41
13  Bill         54
14  Shirley      24
15  Wendy        22
16  Cyrus        33

sales data:

order_num   customer_id amount
1           5           5.00
2           3           10.00
3           12          15.00
4           7           5.00
5           8           15.00
6           13          10.00
7           5           11.00
8           8           12.00
9           12          7.00
10          8           3.00
11          11          5.00
12          14          10.00
13          1           15.00
14          3           16.00
15          16          15.00
16          15          14.00

     select concat( str(( c.age / 10 ) * 10), 's') as Age
          , count(s.order_num) as 'Number_of_sales'
          , sum(s.order_num) as 'Total'
          , AVG(s.order_num) as 'Average Sale'
     from customers c join sales s on c.id = s.amount
     group by ( c.age / 10 )

Produces the following output...

    Age Number_of_sales Total   Average Sale
    20s 6               59      9
    30s 3               33      11
    40s 1               8       8
    50s 6               36      6