GROUP BY
Using group by, we can create groups of related information.
Columns used in select must be used with group by, otherwise it was not a group by expression.
Ex:
SQL> select deptno, sum(sal) from emp group by deptno;
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
SQL> select deptno,job,sum(sal) from emp group by deptno,job;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
HAVING
This will work as where clause which can be used only with group by because of absence of where clause in group by.
Ex:
SQL> select deptno,job,sum(sal) tsal from emp group by deptno,job having sum(sal) > 3000;
DEPTNO JOB TSAL
---------- --------- ----------
10 PRESIDENT 5000
20 ANALYST 6000
30 SALESMAN 5600
SQL> select deptno,job,sum(sal) tsal from emp group by deptno,job having sum(sal) > 3000
order by job;
DEPTNO JOB TSAL
---------- --------- ----------
20 ANALYST 6000
10 PRESIDENT 5000
30 SALESMAN 5600
ORDER OF EXECUTION
- Group the rows together based on group by clause.
- Calculate the group functions for each group.
- Choose and eliminate the groups based on the having clause.
- Order the groups based on the specified column.
Hi There,
ReplyDeleteFully agree on SQL Group By And Having. We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.
I have written a query that essentially unions a new set of data with a previous set of data for a bridge examination.
I need to do this for hundreds of different bridges. Each bridge is identified by a 'guid'.
I have created one table.
The template table contains the bridge elements and current scores assigned for the current bridge exams.
The element table contains the historic elements associated with each bridge but no scores.
These tables are unioned for each guid (bridge identifier) to get all thehistoric elements of each bridge (as current bridge exams may have not examined some historic elements) and then left joined back to the template table to get the current scores for each element. Any elements that have 'null' in the scores after the left joined are updated to 'ne' (not examined)
Anyway this is just the background..
The real question is how do i automate this..
I know you can do a for each loop and then insert those value into the third table.. target template.
I presume i need something along the lines of..
for rob_rec in (select distinct guid from template)
insert into target_template (
MAJORELEMENTCODE,
MAJORELEMENTNO,
MINORELEMENTCODE,
MINORELEMENTNO,
ELEMENTMATERIAL,
SEVERITYEXTENT1,
SEVERITYEXTENT2,
CMSEVERITYEXTENT1,
CMSEVERITYEXTENT2,
COMMENTS,,
GUID
)
I just need to automate the process for each guid in the template table and insert the values into the target_template table.
Thank you very much and will look for more postings from you.
Merci,
Preethi
Olà,
ReplyDeleteGrazie! Grazie! Grazie! Your blog is indeed quite interesting around SQL Group By And Having. I agree with you on lot of points!
how to get the first non space character from a string in Oracle. Could you help me on this?
Anyways great write up, your efforts are much appreciated.
Many Thanks,
Radhey
Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDeleteOracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDelete