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

Source: Internet
Author: User
Tags aliases

--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 Nameto specify the average of a numeric field
SUM ([all| DISTINCT]
Column Namethe sum of the specified number fields
Max ([all| DISTINCT]
Column NameTo specify the maximum value in a numeric field
MIN ([all| DISTINCT]
Column NameTo specify the minimum value in a numeric field
Count ([all| DISTINCT]
Column Nameto 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.)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.