RaqForum 28 No.
530 View  • 
     ...

Getting Monthly Sales Volume for Each Product

 
SQL(179)

【Question】

Here’s a table recording the daily sales volume for each product:

Time                        SalesVolume

2014-01－01 15:20:25           3

2014-02-21 16:11:23            2

2015-01-05 11:14:21            1

2015-02-11 15:21:11            2

This is the format I want:

Month  SalesVolume(2014)  SalesVolume(2015)

1       3                1

2       2                2

3       0                0

4       0                0

5       0                0

 

【Answer】

If the source data is complete and static, we can group data by year and month to get the sales volume and then perform a row-to-column transposition with pivot. But, if the original data is incomplete and dynamic, not all months get shown or the years to-be-shown are not fixed, for example, it’s hard to handle the computation in SQL. Yet, it’s much easier to get this done in SPL:

A

B

1

$select year(Time) year,month(Time)   month,sum(SalesVolume) SalesVolume from tb group by year,month

2

=A1.id()year

3

=A1.align@a(12,)month

4

=create(Month,${A2.(string(~)+"SalesVolume").concat@c()})

5

for A3

>A4.record(#A5|A2.(ifn(A5.select@1(年==A2.~).SalesVolume,0)))

A1: Group data by year and month to get the sales volume in a simple SQL query;

A2: Get distinct year values;

A3: Align A1’s records by the 12 months;

A4: Create an empty table sequence made up of Month, SalesVolume(2014) and SalesVolume(2015) fields;

A5-B5: Loop through A3’s records to populate sales volume in each month for each year into A4’s table sequence.


SQL(179)
 
 •  530 View  •