Posts tagged aggregate
Using TRUNC in SQL to get the first date in a period
0While working on a Web application that required some page to report data aggregated on several levels of time period – by month, quarter or year – I ran into a very handy way to use the TRUNC function. I was not aware of this usage until now – and since I make it a point to encourage my colleagues to write a blog article on anything new they uncover – no matter how trivial it may seem or how strong the feeling that surely everyone already knows about that – I must play by my own rules and tell you about it.
The challenge I was facing can easily be translated into this question: write a SQL query that reports on the number of employees hired per month (or quarter or year). In fact, use a bind parameter that drives the aggregation level – i.e. the Month, Quarter or Year.
Taking the well known EMP table as the target system, this challenge means that all employees hired in the same Month (or Quarter or Year) should be aggregated in the same group. That in turn means that the aggregation should take place by the MONTH (or QUARTER or YEAR) component of their HIREDATE. Instead of fiddling with TO_CHAR using different date identifiers (which also have been an option) it turns out to be More >
AMIS Masterclass Advanced SQL – scherp je SQL skills in een pittige en praktijkgerichte dag – maandag 10 december
0Of je nu ‘klassieke’ Oracle applicaties ontwikkelt, met APEX bezig bent, een SOA landschap inricht of een Java/JEE (bijvoorbeeld ADF) applicatie bouwt: het fundament van veel van wat je doet is de database. En de crux van optimaal gebruik van de database is en blijft SQL. Dit bepaalt performance en schaalbaarheid en ook ontwikkel-productiviteit en zelfs pure functionaliteit. SQL kan veel meer in de Oracle Database dan de meeste – ook heel ervaren ontwikkelaars – weten. Tussen Oracle7 en Oracle 11gR2 is er met eigenlijk iedere release enorm veel kracht bijgekomen in de handen van “SQL componisten”. En je doet jezelf tekort als je niet regelmatig je SQL vaardigheden aanscherpt tot de nieuwste technische mogelijkheden en vooral de praktijkbewezen evolutie.
Op maandag 10 december verzorgt AMIS – Oracle specialist in Nieuwegein, sinds 1991 – een Masterclass Advanced SQL. Waarin voor ervaren SQL ontwikkelaars een overzicht wordt gegeven van de evolutie van de database – tot en met 11g Release 2 en met hier en daar al een vooruitblik naar 12c.
Anti-Search patterns – SQL to look for what is NOT there – Part Two
2It is a strange thing really: querying the database to find missing data. Queries usually report on what is there. Not the queries in this article (and its prequel): they report on what is not there. We explore a number of approaches to uncovering missing information – because the fact that something is not found in itself is a fact we may want to know about. And it gives me a nice motive to demonstrate a number of fun SQL features.
We are still very much engaged in the EMP and DEPT tables in the SCOTT schema that back in the early ’80s was pivotal in demonstrating the capabilities of the first Oracle RDBMS releases. It still does an excellent job for most of my demos today. Must be me…
This article delves into urgent matters such as ‘which letters do not occur in the names of the employees’, ‘in which months of the year was no one hired’, ‘who in each department does not have a colleague with less experience (or a lower salary)’, ‘which employees do not have a colleague in the same job (either in their own department or in the entire company)’, and more.
Recent Comments