200 likes | 386 Views
SQL Tips. IMS User Group Meeting Fall 2002. Aggregation: COUNT. Provides a count of all (distinct) values in a particular column or table. The column can be either alpha or numeric. Null values in the column are included in the count. Syntax COUNT({* | [DISTINCT|ALL] expr}).
E N D
SQL Tips IMS User Group Meeting Fall 2002
Aggregation: COUNT Provides a count of all (distinct) values in a particular column or table. The column can be either alpha or numeric. Null values in the column are included in the count. Syntax COUNT({* | [DISTINCT|ALL] expr})
Aggregation: Count SELECT catalog_nbr,COUNT(*)AS Num_of_Stu FROMPS_DWSA_STDNT_ENRL WHERE acad_group ='TCHE' AND acad_career ='UGRD' AND stdnt_enrl_status ='E' AND subject ='FSOS' GROUPBY catalog_nbr
Aggregation: SUM Provides a sum of all (distinct) values in a particular column. The column must be numeric. Null values in the column are not included in the sum. Syntax SUM([DISTINCT|ALL] n)
Aggregation: SUM SELECT acad_career, subject,SUM(unt_taken) units_taken FROMPS_DWSA_STDNT_ENRL WHERE subject ='SOIL' GROUPBY acad_career, subject
Aggregation: HAVING Use the HAVING clause to restrict which groups of rows defined by the GROUP BY clause are returned by the query.
Aggregation: HAVING SELECT catalog_nbr, class_section,COUNT(*)AS num_of_stu FROMPS_DWSA_STDNT_ENRL WHERE acad_group ='TCHE' AND acad_career ='UGRD' AND stdnt_enrl_status ='E' AND subject ='FSOS' AND component_main ='DIS' GROUPBY catalog_nbr, class_section HAVINGCOUNT(*)>50
Case/Decode/NVL • Case and Decode statements both perform procedural logic inside a SQL statement without having to resort to PL/SQL . • All of these queries will return a list of student names with their secondary email addresses unless they didn’t report a secondary address, then it will return their primary email address. • It is best to use the CASE statement when comparing ranges or more complex logic.
CASE SELECTDISTINCT a.NAME, CASE WHEN a.emailid_2 ISNULLTHEN a.emailid_1 ELSE a.emailid_2 END email_add FROMPS_DWSA_DEMO_ADDR a,PS_DWSA_PROG_DTL b WHERE a.emplid = b.emplid AND b.acad_prog ='32UGR' Case/Decode/NVL
Case/Decode/NVL DECODE SELECTDISTINCT a.NAME, DECODE(a.emailid_2,NULL, a.emailid_1, a.emailid_2) email_add FROMPS_DWSA_DEMO_ADDR a,PS_DWSA_PROG_DTL b WHERE a.emplid = b.emplid AND b.acad_prog ='32UGR'
Case/Decode/NVL NVL SELECTDISTINCT a.NAME, NVL(a.emailid_2, a.emailid_1) email_add FROMPS_DWSA_DEMO_ADDR a,PS_DWSA_PROG_DTL b WHERE a.emplid = b.emplid AND b.acad_prog ='32UGR'
Case/Decode/NVL SELECT a.NAME, (CASE WHEN a.vac_hrs_taken_ytd <=40THEN'GET A LIFE' WHEN a.vac_hrs_taken_ytd BETWEEN41AND100THEN'NEED A BREAK?' WHEN a.vac_hrs_taken_ytd >=101THEN'WELL RESTED' END ) mental_wellbeing FROMPS_DWPY_VAC_SICK a WHERE a.deptid ='831A' AND a.fisc_yr ='2003' AND a.pay_period ='06' AND a.empl_status ='A' ORDERBY2
Aggregation: ROLLUP The use of a ROLLUP clause in the GROUP BY part of the SQL expression displays subtotals and grand totals depending on its use.
Aggregation: ROLLUP SELECTNVL(catalog_nbr,'GRAND_TOTAL') catalog_nbr, class_section, SUM(unt_taken) total_units,COUNT(*) num_of_stu FROMPS_DWSA_STDNT_ENRL WHERE acad_group ='TCHE' AND acad_career ='UGRD' AND stdnt_enrl_status ='E' AND subject ='FSOS' GROUPBY ROLLUP (catalog_nbr, class_section)
INLINE VIEWS You can use a SQL statement in the FROM clause of a SQL statement. This is called an inline view. Oracle treats the data set that is returned from the inline view as if it were a table.
INLINE VIEWS SELECT a.NAME, a.office1_phone FROMPS_DWHR_DEMO_ADDR a, (SELECT x.emplid FROMPS_DWHR_JOB x WHERE x.deptid ='831A' AND x.status_flg ='C' AND x.job_terminated ='N') b WHERE a.emplid = b.emplid;
ROUND Returns a number rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer. Syntax ROUND(n[,m])
WITHOUT: WITH: ROUND SELECTROUND(AVG(eng_act_score),1) FROMPS_DWAD_UGRS_SCRS WHERE eng_act_score !=0 NOTE: There has been an issue with the Web Query tool involving the selection of NUMBER fields that don’t have the scale and precision defined. The error message is returned as‘ERROR: 007~ASP 0101~UNEXPECTED ERROR~THE FUNCTION RETURNED |.’. Use of the ROUND function will alleviate the issue.
A text file with these SQL Tips will be available under the Information section at http://dw.umn.edu