The secondary classification statistics sum sorting-CodePudding

The date, province, city, sale, the four fields, sales date, province, city, sales;

1, to calculate the sales summary of each city, then statistical output all of the biggest cities in the province sales and turnover;
2, the statistical output cities have trading days and the average turnover;

Is there a simple SQL query method of fast efficiency;

CodePudding user response:

 
- to compute each city sales summary 

SELECT the CITY, the SUM (SALE) AS the TOTAL 
FROM the TABLE 
GROUP BY CITY 

- statistical output all of the biggest cities in the province sales and turnover 

SELECT * 
The FROM 
(SELECT *, ROW_NUMBER () OVER (PARTITION BY PROVINCE ORDER BY TOTAL DESC) AS SEQ 
The FROM 
(SELECT PROVINCE, CITY, the SUM (SALE) AS the TOTAL 
FROM the TABLE 
GROUP BY PROVINCE, CITY) AS A) AS B 
WHERE SEQ=1 


Various cities have trading days - statistical output and its average turnover 

The SELECT CITY, COUNT (DISTINCT DATE) AS DT_QTY, (SUM (SALE) * 1.0)/COUNT (DISTINCT DATE) AS AVG_BY_DATE 
FROM the TABLE 
GROUP BY CITY