Shulou(Shulou.com)06/01 Report--

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

NO NAME ITEM MONEY

001 pieces of three wages 80

001 Zhang San Subsidy 86

001 Three Bonus 75

Li Si's Salary 78

Li Si Subsidies 85

002 Li Si Bonus 78

Find the total salary of each person and the total salary of all people. The results are as follows:

The results were 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:

SELECT DECODE(GROUPING(NAME), 1, 'Total', NAME) NAME,SUM(DECODE(ITEM, ' Wage', MONEY, 0)) WAGE,SUM(DECODE(ITEM, 'Subsidy', MONEY, 0)) SUBSIDIARIES,SUM(DECODE(ITEM, ' Bonus', MONEY, 0) BONUS,SUM(MONEY) TOTAL WAGE FROM SALARYGROUP BY ROLLUP(NAME);

Among them: GROUPING function accepts a column, returns 1 if the column value is null, returns 0 if it is not null, and can only be used in queries using ROLLUP or CUBE;

DECODE(ITEM, 'Wage', MONEY, 0) refers to ITME value compared with' Wage', returns MONEY if equal, returns 0 if unequal

Here's another example

There is a test table as follows, (NO student number, cj score)

NO name KM CJ

001 Trilingual 80

001 Zhang San Mathematics 86

001 Zhang San English 75

002 Li Siwen 78

002 Li Si Mathematics 85

002 Li Si English 78

Write the following sentences to calculate the total score of each subject,

select decode(grouping(name),1,'total score', name) name, sum(decode(km, ' math', cj,0)) math,sum(decode(km, 'english', cj,0)) english, sum(decode(km, ' language', cj,0)) chinese,sum(cj) total score from test group by rollout (name)

The results were as follows:

Name Mathematics English Chinese Total score Li Si 85 78 78 241 Zhang San 86 75 80 241 Total score 171 153 158 482

summary

Above is this article on oracle horizontal vertical sum code examples of all content, I hope to help you. Interested friends can refer to: ORACLE SQL statement optimization technical points analysis, oracle database import TXT file method introduction, ORACLE multi-condition statistical query simple method, etc. If you have any questions, please feel free to leave a message. Welcome to exchange and discuss.

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.