1 / 20

SQL Tips

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}).

aimee
Download Presentation

SQL Tips

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Tips IMS User Group Meeting Fall 2002

  2. 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})

  3. 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

  4. 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)

  5. Aggregation: SUM SELECT acad_career, subject,SUM(unt_taken) units_taken FROMPS_DWSA_STDNT_ENRL WHERE subject ='SOIL' GROUPBY acad_career, subject

  6. Aggregation: HAVING Use the HAVING clause to restrict which groups of rows defined by the GROUP BY clause are returned by the query.

  7. 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

  8. 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.

  9. 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

  10. 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'

  11. 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'

  12. 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

  13. Case/Decode/NVL

  14. 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.

  15. 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)

  16. 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.

  17. 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;

  18. 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])

  19. 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.

  20. A text file with these SQL Tips will be available under the Information section at http://dw.umn.edu

More Related