Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve summation in oracle, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

There is a payroll SALARY as follows, (NO employee number, MONEY salary)

NO NAME ITEM MONEY

001 Zhang San salary 80

001 Zhang San subsidy 86

001 Zhang San bonus 75

002 Li Si's salary 78

002 Li Si subsidy 85

002 Li Si bonus 78

Ask for everyone's total salary and everyone's total salary, the results are as follows:

The results are as follows:

Name salary subsidy bonus total salary

Li Si 780 850 780 2410

Zhang San 800 860 750 2410

Total 1580 1710 1530 4820

SQL statement:

Name of SELECT DECODE (GROUPING (NAME), 1, 'Total', NAME), SUM (DECODE (ITEM, 'salary', MONEY, 0)) salary, SUM (DECODE (ITEM, 'subsidy', MONEY, 0)) subsidy, SUM (DECODE (ITEM, 'bonus', MONEY, 0)) bonus, SUM (MONEY) Total salary FROM SALARYGROUP BY ROLLUP (NAME)

Where: the GROUPING function accepts a column, returns 1 if the column value is empty, and 0 if it is not empty. It can only be used in queries using ROLLUP or CUBE.

DECODE (ITEM, 'salary', MONEY, 0) means that the value of ITME is compared with the value of 'salary'. If equal returns MONEY, unequal returns 0

The above is how to achieve summation in oracle. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.