Summary of SQL Statistical statements (Application scenario: operational analysis, financial analysis, etc.)

--statistics on the amount of data per day for MarchSelect Count(*), substr (T.date,1,Ten) from TableTwhereT.date like '2010-03%' Group  bySUBSTR (T.date,1,Ten) ;--statistics from May to 1 September-June 29 Data VolumeSELECTSUBSTR (A.feed_publish_time,1,Ten) as 'Date',Count(*) as 'Medical Talk Number'  fromXm_feed AWHEREA.feed_publish_timebetween '2015-05-19 00:00:00'  and '2015-06-29 23:59:59' GROUP  bySUBSTR (A.feed_publish_time,1,Ten);

SQL The statement counts daily, monthly, yearly Data

1, AnnualSelect  Year(Ordertime) years,sum(total) Sales totals fromOrder FormGroup  by  Year(ordertime)2, monthlySelect  Year(Ordertime) years,Month(ordertime) month,sum(total) Sales totals fromOrder FormGroup  by  Year(ordertime),Month(Ordertime3, DailySelect  Year(Ordertime) years,Month(ordertime) month, Day(Ordertime) Day,sum(total) Sales totals fromOrder FormGroup  by  Year(ordertime),Month(ordertime), Day(Ordertime) In addition also daily can this:Select Convert(Char(8), Ordertime, the) DT,sum(total) Sales totals fromOrder FormGroup  by Convert(Char(8), Ordertime, the)

SQL question how to count the daily records in one months

How do you write it?  Write it Up! For example, to check the records of March 2010 every day.

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 for date here is Yyyy-mm-dd Hh:mm:ss

SQL data is divided into monthly statistics, the table only the daily data, now requires a year of the monthly statistics (a SQL )

SELECT

The field of that date),

Like sales or something)

From

Table

WHERE

Let's say you want to check it out. Monthly statistics of the year. 

GROUP by

field of that date)

with SELECT statement to summarize data

Statistical summarization of data with a SELECT statement
Aggregate functions (aggregation functions, statistical functions)
To effectively handle the collection of data that the query obtains, SQL Server provides a series of statistical functions.

These functions can implement data collection and summarization:
avg ([all| DISTINCT]Column Name) to specify the average of a numeric field
SUM ([all| DISTINCT]Column Name) the sum of the specified number fields
Max ([all| DISTINCT]Column Name) To specify the maximum value in a numeric field
MIN ([all| DISTINCT]Column Name) To specify the minimum value in a numeric field
Count ([all| DISTINCT]Column Name) to satisfy the number of records in a condition record that are not empty in the specified field
COUNT (*)To satisfy the total number of condition records
**********************************************************************************************
Classify records by the GROUP BY clause statistical summary
Format:
Group by Group field Name list [having conditional expression]
Function: Group the specified fields in sequence by the specified criteria to summarize the statistics

Note:
A statement using group by can still be ordered by the ORDER BY clause
However, you must be able to use aliases after group by but not to sort columns that are not specified by select
The HAVING clause filters the query results after grouping statistics.
A SELECT statement using group by can still use the WHERE clause to specify conditions
**********************************************************************************************
Description
The field specified by Select must contain and include only the grouping field specified in the GROUP BY clause (you can specify an alias for it).
The other must be one or more computed columns that consist of aggregate functions, and the columns used in the statistics function are not restricted.
The alias of a field or computed column is not allowed in the GROUP by clause, and the expression can be used directly.
When a GROUP by clause specifies an expression, the field in the select specified can be excluded from the expression.
The HAVING clause does not allow the use of aliases
The HAVING clause must be used with group by, and the conditions set must be related to the Group field specified by the GROUP BY clause

Summary of SQL Statistical statements (Application scenario: operational analysis, financial analysis, etc.)