Mysql_ Financial Statistics each product category cities last week income margin table _20161202

Note: Financial needs statistics of each product category cities last week the following table is the form of post-communication presentation. Data presentation Form I think most of the row-to-row switch-over from the financial point of view this form of presentation is more appropriate.

Because the yellow part is a summary item, I split it into 9 conversion steps similar to vertical merging is just a summary and different categories of the fields can not be vertically merged so with the kettle when the individual steps together, the product classification dimension is divided into the respective steps to fill in Excel

1, #C024_01销售总收入SQL in order to make it easier to find each SQL preceded by annotated numbering facilitates code replication and lookups

#销售总收入汇总SELECT ' total sales revenue ' as sales revenue, SUM (if (city = "City A", sales confirmation, null)) as City A,sum (if (city = "City B", sales confirmation, NULL)) as City B,sum (if (city = " City C ", sales confirmation, null)) as City C,sum (if (city =" City D ", sales confirmation, null)) as City D,sum (if (city =" City E ", sales confirmation, null)) as City Efrom (#上周一到本周一SELECT City, SUM (sales) as sales confirmation from ' a005_account ' WHERE receivable date >=date_add (date (Date_add (Current_date,interval-weekday (Current_ Date)), INTERVAL-1 WEEK) and receivable date <date (Date_add (current_date,interval-weekday) day) GROUP by city) as A

2, #C024_02 last week category sales revenue

# #C024_02 last week category sales revenue select  CONCAT (b. Category ID, '-', A. Product category) as commodity category, SUM (if (city = "City A", sales confirmation, NULL)) as City A,sum (if (city = "City B", Sales confirmation, NULL) as City B,sum (if (city = "City C", sales confirmation, null)) as City C,sum (if (city = "City D", sales confirmation, null)) as City D,sum (if (city = "City E", sales confirmation, NULL) as City Efrom (#上周一到本周一SELECT City, A2. Product Classification, SUM (sales) as sales acknowledgement from ' A005_account ' as A1left JOIN ' a002_ product ' as A2 on A1. Product I D=a2. Product Idwhere Receivable Date >=date_add (date (Date_add (Current_date,interval-weekday) day), Current_date INTERVAL-1) and receivable Date <date (Date_add (Current_date,interval-weekday (current_date)) GROUP by city, A2. Product category) as Aleft JOIN ' a000_ Cate ' as B on a. Product Classification =b. Product Classification Group by a. Product category Order BY B. Category ID

3. Summary of#C024_03 cost sum #C024_04品类cost and gross profit sum category margin is the same as above it's just the sum (field) different can omit the code

4, #C024_09 last week coupon amount

#C024_09 Last week coupon Amount Select  

5. Set Excel template Kettle Conversion job

6, the data show that the real data has been processed the following data and the actual logical values range

Mysql_ Financial Statistics each product category cities last week income margin table _20161202