How to use php to query monthly sales: 1. Create a php sample file; 2. Use php to connect to the database; 3. Use the SQL statement "select year(ordertime), month(ordertime),sum( Total) from xxx group by year(ordertime),month(ordertime)" can be used to query and count monthly sales data.

The operating environment of this tutorial: Windows 7 system, PHP version 8.1, Dell G3 computer.

php How to check monthly sales?

SQL statement counts daily, monthly, and yearly data

Demo

Every year

select year(ordertime) 年，
sum(Total) 销售合计
from 订单表
group by year(ordertime)

Every month

select year(ordertime) 年，
month(ordertime) 月,
sum(Total) 销售合计
from 订单表
group by year(ordertime)，
month(ordertime)

Daily

select year(ordertime) 年，
month(ordertime) 月,
day(ordertime) 日，
sum(Total) 销售合计
from 订单表
group by year(ordertime)，
month(ordertime),
day(ordertime)

Example

SQL question How to statistically query the records of each day in a month

For example, to check March 2010 Daily records

Answer

select count(*),substr(t.date,1,10)  from table t where  t.date like '2010-03%' group by substr(t.date,1,10)

The format of date here is YYYY-mm-dd hh:mm:ss

sql data is divided into monthly statistics, and there are only Daily data, now we require statistical data for each month of the year (a sql)

SELECT
  MONTH (  那个日期的字段  ),
  SUM(  需要统计的字段， 比如销售额什么的 )
FROM
  表
WHERE
  YEAR (  那个日期的字段  ) = 2010   -- 这里假设你要查 2010年的每月的统计。
GROUP BY
 MONTH (  那个日期的字段  )

Use SELECT statements to statistically summarize data

Use SELECT statements to statistically summarize data
Aggregation functions (aggregation functions, statistical functions)
In order to effectively process the data collection obtained by the query, SQL Server provides a series of statistical functions.

These functions can implement data collection and summary:
avg ([ALL|DISTINCT] column name) Find the average of the specified numeric field
sum ([ALL|DISTINCT] column name) Find the sum of the specified numeric field
max ([ALL|DISTINCT] column name) Find Find the maximum value in the specified numeric field
min ([ALL|DISTINCT] column name) Find the minimum value in the specified numeric field
count ([ALL|DISTINCT] column name) Find the specified field in the record that satisfies the condition and is not empty Number of records
count(*) Find the total number of records that meet the conditions

Use the GROUP BY clause to classify and summarize records
Format:
GROUP BY grouping field name list[HAVING Conditional expression]
Function: Group the specified fields in sequence according to the specified conditions for statistical summary

Note:
Statements using GROUP BY can still be sorted by the ORDER BY clause
But they must be in GROUP You can use aliases after BY but you are not allowed to sort columns not specified by SELECT
The HAVING clause is to filter the query results after group statistics.
Select statements using GROUP BY can still use the WHERE clause to specify conditions

Note:
The field specified by Select must contain and only contain the grouping field specified in the GROUP BY clause (an alias can be specified for it),
Others must be composed of an aggregate function or multiple calculated columns, the columns used in statistical functions are not restricted.
Aliases of fields or calculated columns are not allowed in the GROUP BY clause, expressions can be used directly.
GROUP BY clause specifies expressions When formula is used, the field specified by select does not need to include this expression.
HAVING clause does not allow the use of aliases
HAVING clause must be used together with GROUP BY, and the set conditions must be consistent with those specified by the GROUP BY clause Recommended learning about grouping fields: "

"

The above is the detailed content of How to check monthly sales in php. For more information, please follow other related articles on the PHP Chinese website!