Two SQL statements that were not written in a long time (group by,having)

1, statistics of the average salary of the departments, the mean capital (required to show the department name, department number, total number of departments)

Sql> SELECTDname department Name, D.deptno department number,COUNT(ename) Total number of departments,ROUND(AVG(NVL (SAL,0)),2) Department average salary,ROUND(AVG(NVL (COMM,0)),1) Departmental average funding
 fromEMP E Right JOINDEPT D
 onE.deptno=D.deptno
GROUP  byDname,d.deptno
     ORDER  byD.deptno;
Department Name Department Number department total number of departments average salary department fund-------------- ---------- ---------- ------------ ------------ACCOUNTINGTen          3      2916.67            0 the -          5         2175            0SALES -          6      1566.67        366.7OPERATIONS +          0            0            0

2, ibid, only show the department number is 10,20,30 information

Sql> SELECTDname department Name, D.deptno department number,COUNT(ename) Total number of departments,ROUND(AVG(NVL (SAL,0)),2) Department average salary,ROUND(AVG(NVL (COMM,0)),1) Departmental average funding
 fromEMP E Right JOINDEPT D
 onE.deptno=D.deptno
GROUP  byDname,d.deptno
 havingD.deptnoinch(10, -, -)
ORDER  byD.deptno;
Department Name Department Number department total number of departments average salary department fund-------------- ---------- ---------- ------------ ------------ACCOUNTINGTen          3      2916.67            0 the -          5         2175            0SALES -          6      1566.67        366.7SQL>

Parse: Use the NVL function to handle null values, leaving the null value at 0, because the AVG function ignores null values when calculating the mean. Use the round function for rounding. The HAVING clause is used to filter the grouped results, so having cannot be placed after the order by condition. Similarly, you can use the sum, MAX, min functions to count.

Sql> SELECTDname department Name, D.deptno department number,COUNT(ename) Total number of departments,ROUND(AVG(NVL (SAL,0)),2) Department average salary,ROUND(AVG(NVL (COMM,0)),1)
Department average fund, NVL (MAX(SAL),0) maximum wage, NVL (MIN(SAL),0) minimum wage, NVL (SUM(SAL),0) Department payroll total expenditure, NVL (MAX(COMM),0) maximum funding,NVL (MIN(COMM),0)
Minimum bonus, NVL (SUM(COMM),0) Total Department bonuses fromEMP E Right JOINDEPT D
 onE.deptno=D.deptno
GROUP  byDname,d.deptno
ORDER  byD.deptno;
Department Name Department Number department total number of departments average salary department median capital maximum wage minimum wage department total wage expenditure Maximum fund minimum Bonus Department bonus total--------- -------- ---------- ---------  -------   ------     ---------- ------- ---------- -------- ----------ACCOUNTINGTen          3      2916.67       0        the       1300       8750          0     0            0 the -          5         2175       0        the         -      10875          0     0            0SALES -          6      1566.67   366.7       2850        950       9400       1400     0         2200OPERATIONS +          0            0       0          0          0          0          0     0            0SQL> 

Two SQL statements (group by,having) that were not written for a long time