Oracle/sql use functions for yearly, monthly, weekly, daily data summaries

15/03/21 use functions for yearly, monthly, weekly, daily data summaries

Suppose a sales schedule sale_detail contains a country (country), sales time (sale_time), sales ( Sale_money ) .. wait

The SUM function sum() and grouping clauses need to be summed up by country and year groupings (sum each year for each country)

There are two forms of statements:

Yearly Summary

1. Forms such as:

Countries	Year	Sales
Brazil	2014	500000000000

Way One:

SELECT S.country as national --- as can be omitted

To_char (s.sale_time, ' YYYY ') as year ---to_char () character Format function, converts the time format to a four-bit year, 'YYYY' Character Format Display

Sum(s.sale_money) as sales --- summarize amounts that meet national year conditions

From Sale_detail S--- data source is sale_detail

WHERE sale_time>=to_date (' 2014-01-01 ', ' YYYY-MM-DD ')---the to_date () time Format function, which converts the character to the time format. Determine the time range in the form of a time range for which year.

and Sale_time<=to_date (' 2015-01-01, ' yyyy-mm-dd ')

GROUP by S.country,sale_time--- the WHERE to query data by country, time summary

Having Sum(s.sale_money)>10000000-- The results of the packet query, and only selects more than 10000000- of data.

Way two:

SELECT S.country as national --- as can be omitted

To_char (s.sale_time, ' YYYY ') as year ---to_char () character Format function, converts the time format to a four-bit year, 'YYYY' Character Format Display

Sum(s.sale_money) as sales --- summarize amounts that meet national year conditions

From Sale_detail S--- data source is sale_detail

WHERE to_char (sale_time, ' YYYY ') = '--to_char ' () character converts the Format function to convert the time format to a character. Find The data that contains the year.

GROUP by S.country,sale_time--- the WHERE to query data by country, time summary

Having Sum(s.sale_money)>10000000-- The results of the packet query, and only selects more than 10000000- of data.

Monthly Summary

1. The form of table is as follows

Countries	Month	Sales
Brazil	2014/05	6000000

SELECT S.country as national --- as can be omitted

To_char (s.sale_time, ' yyyy/mm ') as year ---the To_char () character formatting function, converting the time format to a four-bit year , Two-bit month, 'yyyy/mm' character format display

Sum(s.sale_money) as sales --- summarize amounts that meet national year conditions

From Sale_detail S--- data source is sale_detail

WHERE to_char (sale_time, ' YYYY ') = '--to_char ' () character converts the Format function to convert the time format to a character. Find The data that contains the year.

GROUP by S.country,sale_time--- the WHERE to query data by country, time summary

ORDER by S.sale_time

2. The form of the table is as follows:

Countries	Year	Month	Sales
Brazil	2014	1	600000
Brazil	2015	2	600000

SELECT S.country as national --- as can be omitted

To_char (s.sale_time, ' YYYY ') as year

To_char (s.sale_time, ' MM ') as month ---to_char () character Format function, converts the time format to a four-bit year , Two-bit month, ' MM' character format display

Sum(s.sale_money) as sales --- summarize amounts that meet national year conditions

From Sale_detail S--- data source is sale_detail

WHERE to_char (sale_time, ' YYYY ') in (' All ', '} ') the--to_char () character converts the Format function to convert the time format to a character. Find data that contains 2014,2015 years.

GROUP by S.country,sale_time--- the WHERE to query data by country, time summary

ORDER by S.country,,s.sale_time

Oracle/sql use functions for yearly, monthly, weekly, daily data summaries