MySQL combined with Excel sum sumif sumifs function _ Brand Summary _20161101

The calculation of some numbers is not difficult, the whole is to cultivate the logic of self-awareness, logic clear, the code naturally know that the overall training of their own logic, one is from the user perspective, one from the product perspective.

User perspective needs to consider is the user's activity, and can be subdivided into the user's registration date, the next single interval, the user's various time dimensions of the order number, order amount, etc.

Product angle consider product classification, product brand, product supplier angle and so on.

A demand today is to talk about some brand partners, need some data, statistics brocade 3 months of sales and amount, the procurement department to provide some brands.

If you follow the traditional numbers, just add the brand in the Where condition (brand 1, Brand 2, Brand 3, etc.) only give him this data can be achieved, but if next time he would like to examine the data outside the brand is not also need to modify the where condition to limit not only so where in these conditions limit It's more trouble to play.

The better logic is to put all the brand's gold 3 months of detailed summary, through the Excel sumif sumifs function

The SQL of the brand summary is modified by the following data

SELECTD.id,c. City, c. brand name, c. Month,COUNT(c. Product ID) asSKU number,SUM(Order Amount) asOrder Amount,SUM(packing amount) asPacking Amount from (    SELECTA. City, a. Product id,a. Product name, B. Brand name, Date_format (A. Order date, "%Y%M ") asYearsSUM(A. Amount) asOrder Amount,SUM(' Rollup packing amount ') asPacking Amount from' A003_order ' asa Left JOIN' A002_sku ' asB onA. Product ID=B. Product IDWHEREA. Order Date>=" .- ,- on" andA. Order Date<" .- One- on" andA. Amount>0    GROUP  byA. City, a. Product Id,date_format (A. Order date, "%Y%m "))  asC Left JOIN' A000_city ' asD onD. City=c. CityGROUP  byC. City, c. brand name, c. MonthORDER  byD.id,c. Month

Excel: Brand summary sheet table data

This Excel sheet table brand is not only a ABCD4 brand, but a number of brands

We present the data in the following format, using the two functions we have just mentioned

Later if he wants to see what time period of the brand we only need to modify the where time limit can be at the same time the need to show the brand to replace it.

SUMIF () formula: =SUMIF (brand summary! $C: $C, Data! $B 4, Brand summary! G:G) that is (condition area A, condition B, Sum area C) Where Condition B is in the condition area A

That is, the sum of Area C of condition B

Brand Summary! $C: $C: This is the selected brand summary table in column C and brand column which is the conditional area

Data! $B 4: This is the data in the B4 cell that brand a can find this brand a in the above conditions

Brand Summary! G:g: This is the selected brand summary table in column G and brand column This is the sum area and the packing amount

Explain the sum of the packing amount of brand A in the brand summary table.

SUMIFS (brand Summary! $F: $F, Brand summary! $C: $C, Data! $B 4, Brand summary! $B: $B, f$3, Brand summary! $D: $D, 201608)

Sumifs (summation area, condition area A, condition A, condition B, condition B) condition A in the condition area a condition B within the condition B

It also uses the absolute reference relative reference semi-absolute reference to drag the formula directly to

The results are as follows

If you want to see the brand ABC and SD September of the formula in the full selection of data sheet table 201608 into 201609 data sheet table D to SD can be

It's easy to operate autonomously.

MySQL combined with Excel sum sumif sumifs function _ Brand Summary _20161101