Tuesday, 28 February 2017


*  GROUP BY clause
*  Group functions
*  HAVING clause

In this chapter, we will see how to group data to get summary information. We have so far seen WHERE, ORDER BY clauses of SELECT command. In this chapter, we will see GROUP BY and HAVING clauses.  We will also see how to use group functions and two new function introduced in Oracle8i  - ROLLUP and CUBE.

Some times, we have to group the rows of the table by a particular column and perform certain operations on each group. For example, you may want to display the number of students in each batch.  For this we have to group rows of STUDENTS table based on the column BCODE. This will create groups – one for each unique batch code.  Then using group function COUNT we can count the number of rows (equivalent to number of students) in each group.

Now let us see how the above-mentioned task can be accomplished. The following SELECT command groups rows of STUDENTS table on BCODE using GROUP BY clause. Then it counts number of students we have in each batch using group function COUNT.

select  bcode, count(rollno)
from  students
group by bcode;

----- -------------
b1                2
b2                3
b3                2
b4                1
b5                3

We will see one more example. In this we will display the total amount of fee paid by each student. For this we use PAYMENTS table and group function SUM. Here is the required query.

select  rollno, sum(amount) from payments group by rollno;

--------- -----------
        1        4500
        2        4500
        3        5000
        4        5000
        5        5000
        6        3500
        7        3500
        8        4000
        9        3000
       10        6500
       11        4500

While you are grouping the data the following points are to be taken in to account.

q  Find out the table from where the data is to be taken.
q  Find out the column or columns on which the data is to be grouped.
q  Find out the group function that is to be used to get required aggregate.

GROUP BY clause is used to specify the column(s) on which the rows of the table are to be grouped.  It means it divides the rows into different groups based on the column(s) that are given in GROUP BY clause. For example, in the above example, the rows of STUDENTS table are to be grouped based on the value of column ROLLNO. So all rows that contain the same roll number will be taken as one group. Like this the number of groups will be equivalent to the number of unique roll numbers in the table.

2.            Points to remember
It is important to remember that only the following can be selected in SELECT while you are grouping the data.

q  The columns that are given in GROUP BY clause can be selected.
q  Group function

Group Functions
Group functions are the functions that are used to perform operations on groups.  Table 1 lists all the available group functions. The general syntax of group functions is given below.

3.       group-function (DISTINCT  column)

If DISTINCT option is used then only distinct (unique) values are taken into account.  The following query is used to count the number of students who have paid some amount.

select  count(rollno) from payments;


But the count includes the duplicates. That means, if the same student has made the payment twice then his number will be counted for twice.  But what if I want to count how many students have made some payment without taking duplicates?. The answer is DISTINCT clause in the group function as shown in the next query.

select  count(DISTINCT rollno) from payments;


The DISTINCT clause is making COUNT function count only distinct value and not all values.

4.            Note: COUNT(*) counts the number of rows  in each  group. Asterisk (*) refers to the entire row. You can use any column name instead to count the number of not null values in that column.

Group Function
Returns the sum of the given column.
Returns the average of the given column.
Returns  the minimum value in the given column.
Returns  the maximum  value in the given column.
Returns  the standard deviation of the values in the given column.
Returns  the variance of the values in the given column..
Returns the number of not null values in the given column. Returns number of rows, if  * is given instead of column.
Table 1: GROUP functions.

The following query displays the subject and date on which the most recent batch of that subject has started.

select  ccode,max(stdate)
from batches
group by ccode;

----- ---------
asp   15-JAN-01
c     20-JAN-01
java  05-APR-01
ora   15-AUG-01
vbnet 12-JUL-01
xml   02-MAR-01

It is also possible to use group functions without using GROUP BY clause. In this case the entire selected rows will be taken as a single group and group function performs the operation on the entire set of selected rows.

The following query will display the highest course fee.

select max(fee) from courses;


5.            GROUP BY and WHERE clause
If WHERE clause is used along with GROUP BY then WHERE clause is executed first.  The rows that are selected by WHERE clause will be grouped by GROUP BY clause. The following query shows the number of batches that started for each subject in the last 3 months.

select  ccode, count(*) count
from  batches
where   months_between(sysdate,stdate) <= 3
group by ccode;

----- ---------
ora           1
vbnet         1

In the above query, first rows that satisfy the given condition are retrieved. Then these rows are passed to GROUP BY clause for grouping.

6.            Note: GROUP BY clause should follow WHERE clause, if both are used together.

WHERE clause is used to select the rows that are to be used for grouping.

7.            Grouping on more than one column
It is possible to group rows on more than one column. In this case, the first columns is used to group the data, and then within that group records are again grouped based on second column, and so on.

The following query displays the  date on which the last batch  was taken by each faculty for each subject.

select  fcode, ccode, max(stdate)
from  batches
group by  fcode,ccode;

----- ----- ---------
da    asp   15-JAN-01
da    vbnet 12-JUL-01
dh    xml   02-MAR-01
gk    ora   12-JAN-01
hs    c     20-JAN-01
hs    java  05-APR-01
jc    ora   15-AUG-01

What if  you have to select  only  a   few groups   based on the condition  that is using the result of one of the  group functions?.  For example, what if  you have to display  the batches that have more than 10 students. HAVING clause is used for this purpose. It is used to filter groups based on the given condition. The following example shows the courses that have got more than 1 batch.

select  ccode
from batches
group by  ccode
having count(*) > 1;


In the above example, first rows of BATCHES table are grouped on CCODE. Then the query selects the groups that contain more than 1 row.  The later is achieved using HAVING clause, which is specially meant to select groups based on the given condition.

Here is a list of important points that are to be noted:

q  WHERE clause can be used to check for conditions based on values of columns and expressions related to individual rows. It cannot be used with conditions related to groups.
q  HAVING clause is specially designed to evaluate the conditions that are based on group functions such as SUM, and COUNT.
q  HAVING clause cannot be used for conditions that are not related to groups.

The following few example will illustrate the above-mentioned important points.

SQL> select  ccode
  2  from batches
  3  where count(*) > 1
  4  group by ccode;
where count(*) > 1
ERROR at line 3:
ORA-00934: group function is not allowed here

The above example returns error because WHERE clause cannot be used with group functions.

SQL> select  ccode, count(*)
  2  from batches
  3  group by ccode
  4  having  enddate is null;
having  enddate is null
ERROR at line 4:
ORA-00979: not a GROUP BY expression

In the above example as we tried to use normal condition using HAVING clause Oracle returned an error saying that the given expression is not acceptable.

The following query is valid as HAVING clause is used with a condition that is related to group by expression – CCODE.

SQL> select ccode, count(*)
  2  from batches
  3  group by ccode
  4  having  length(ccode) > 3;

----- ---------
java          1
vbnet         1

8.            Using WHERE and HAVING together
It is possible to use both WHERE and HAVING clauses together. When these two clauses are used together, first WHERE clause is to be given then HAVING clause.

Oracle first selects rows based on WHERE clause. After that it groups the selected data. Then groups are selected based on HAVING clause.

The following query displays the courses that we started for more than once in the last six months.

select ccode, count(*)
from batches
where  months_between(sysdate,stdate) <= 6
group by ccode
having  count(*) > 1;

----- ---------
ora           2

9.            ORDER BY with GROUP BY
ORDER BY clause can be used along with GROUP BY clause to order the results. When used ORDER BY clause must be the last clause to be used in the query.

select fcode, count(*)
from course_faculty
group by fcode
order by  count(*);

----- ---------
dh            1
gk            1
kl            1
jc            1
sw            1
da            2
hs            2
jj            3

10.          Note: When ORDER BY is used with GROUP BY clause, ORDER BY should have either the group by column or group function.

11.         Order of execution
Here is the order Oracle uses to execute different clauses given in SELECT command.

q  Selects rows based on WHERE clause.
q  Groups rows based on GROUP BY clause.
q  Calculates results for each group.
q  Eliminates groups based on HAVING clause.
q  Then uses ORDER BY to order the results.

Oracle8i enhanced grouping by adding ROLLUP and CUBE. These two provide totals at multiple dimensions.

The following is the syntax of ROLLUP.

12.     SELECT ...
13.     GROUP BY ROLLUP (columns);

ROLLUP creates sub totals from most detailed to grand total.  It moves from right to left in the list of columns given in ROLLUP.

Before we use ROLLUP, let us see the result of simple group by clause using BATCHES table.

select ccode,fcode, count(*)
from  batches
group by ccode,fcode;

----- ----- ---------
asp   da            1
c     hs              1
c     kl              1
java  hs            1
ora   gk            2
ora   kl              1
vbnet da            2
xml   dh            1

In case of normal GROUP BY clause we get count of each course and faculty. But what if you want to get total number of batches for each course and also the total number of batches.

The following ROLLUP creates the required aggregates.

select ccode,fcode, count(*)
from  batches
group by rollup(ccode,fcode);

----- ----- ---------
asp   da            1
asp                  1
c     hs              1
c     kl              1
c                      2
java  hs             1
java                 1
ora   gk            2
ora   kl             1
ora                  3
vbnet da           2
vbnet               2
xml   dh             1
xml                   1

Apart from rows that come from GROUP BY, ROLLUP generates new rows that display the number of batches for each course and also total number of batches.

In case of total number of batches of a single course the CCODE is contains course code and FCODE contains null. In case of total number of batches both CCODE and FCODE contain null value.

The following query will display a meaningful value for these columns.

select nvl(ccode,'ALL courses'), nvl(fcode,'All faculty'), count(*)
from  batches
group by rollup(ccode,fcode);

----------- ----------- ---------
asp         da                      1
asp         All faculty          1
c           hs                       1
c           kl                        1
c           All faculty            2
java        hs                      1
java        All faculty          1
ora         gk                      2
ora         kl                       1
ora         All faculty           3
vbnet       da                     2
vbnet       All faculty          2
xml         dh                      1
xml         All faculty            1
ALL courses All faculty    10

14.         GROUPING function
This will return a value of 1 if the column’s value is generated by ROLLUP. So we can use DECODE and GROUPING functions to get the same result as the above.

select decode(grouping(ccode),1,'ALL courses',ccode)  ccode,
       decode(grouping(fcode),1,'All faculty',fcode)  fcode,
       count(*) count
from  batches
group by rollup(ccode,fcode);

CCODE       FCODE           COUNT
----------- ----------- ---------
asp         da                    1
asp         All faculty         1
c           hs                       1
c           kl                       1
c           All faculty           2
java        hs                     1
java        All faculty          1
ora         gk                     2
ora         kl                      1
ora         All faculty          3
vbnet       da                     2
vbnet       All faculty         2
xml         dh                      1
xml         All faculty          1
ALL courses All faculty   10

This generates the same subtotals as ROLLUP and plus a few more. This provides all possible subtotals.  For example in the previous output of ROLLUP we got the number of batches taken by each faculty for each course, number of batches for each course and total number of bathes.

CUBE apart from generating all these can also generate subtotals for each faculty. That means it provides number of batches taken by each faculty also.

select decode(grouping(ccode),1,'ALL courses',ccode)  ccode,
       decode(grouping(fcode),1,'All faculty',fcode)  fcode,
       count(*) count
from  batches
group by cube(ccode,fcode);

CCODE       FCODE           COUNT
----------- ----------- ---------
asp         da                          1
asp         All faculty              1
c           hs                           1
c           kl                           1
c           All faculty               2
java        hs                          1
java        All faculty              1
ora         gk                         2
ora         kl                          1
ora         All faculty              3
vbnet       da                        2
vbnet       All faculty             2
xml         dh                         1
xml         All faculty              1
ALL courses da                   3
ALL courses dh                   1
ALL courses gk                   2
ALL courses hs                   2
ALL courses kl                    2
ALL courses All faculty       10

CUBE adds five more rows to the output of ROLLUP. Each of these new rows is to display the total number of batches taken by each faculty for all courses.

GROUP BY clause is used to group the rows of the table based on the given columns. Group functions can be used to calculate aggregates like average of each group. HAVING clause is used to filter groups based on the result of group function. Oracle executes first WHERE then GROUP BY then HAVING and finally ORDER BY.

Calendar in a JComboBox

import java.awt.*;
import java.awt.event.*;
import java.util.Calendar;
import java.text.SimpleDateFormat;

import javax.swing.*;
import javax.swing.event.PopupMenuListener;
import javax.swing.event.PopupMenuEvent;
import javax.swing.plaf.ComboBoxUI;
import javax.swing.plaf.basic.ComboPopup;
import javax.swing.plaf.metal.MetalComboBoxUI;
import javax.swing.border.Border;
import javax.swing.border.EtchedBorder;
import javax.swing.border.EmptyBorder;

import com.sun.java.swing.plaf.motif.MotifComboBoxUI;
import com.sun.java.swing.plaf.windows.WindowsComboBoxUI;

public class DateComboBox extends JComboBox {

    protected SimpleDateFormat dateFormat = new SimpleDateFormat("MMM d, yyyy");
    public void setDateFormat(SimpleDateFormat dateFormat) {
    this.dateFormat = dateFormat;
    public void setSelectedItem(Object item) {
    // Could put extra logic here or in renderer when item is instanceof Date, Calendar, or String
    // Dont keep a list ... just the currently selected item
    removeAllItems(); // hides the popup if visible

    public void updateUI() {
    ComboBoxUI cui = (ComboBoxUI) UIManager.getUI(this);
    if (cui instanceof MetalComboBoxUI) {
        cui = new MetalDateComboBoxUI();
    } else if (cui instanceof MotifComboBoxUI) {
        cui = new MotifDateComboBoxUI();
    } else if (cui instanceof WindowsComboBoxUI) {
        cui = new WindowsDateComboBoxUI();

    // Inner classes are used purely to keep DateComboBox component in one file
    // UI Inner classes -- one for each supported Look and Feel

    class MetalDateComboBoxUI extends MetalComboBoxUI {
    protected ComboPopup createPopup() {
        return new DatePopup( comboBox );

    class WindowsDateComboBoxUI extends WindowsComboBoxUI {
    protected ComboPopup createPopup() {
        return new DatePopup( comboBox );

    class MotifDateComboBoxUI extends MotifComboBoxUI {
    protected ComboPopup createPopup() {
        return new DatePopup( comboBox );

    // DatePopup inner class

    class DatePopup implements ComboPopup, MouseMotionListener,
                   MouseListener, KeyListener, PopupMenuListener {

    protected JComboBox comboBox;
    protected Calendar calendar;
    protected JPopupMenu popup;
    protected JLabel monthLabel;
    protected JPanel days = null;
    protected SimpleDateFormat monthFormat = new SimpleDateFormat("MMM yyyy");

    protected Color selectedBackground;
    protected Color selectedForeground;
    protected Color background;
    protected Color foreground;

    public DatePopup(JComboBox comboBox) {
        this.comboBox = comboBox;
        calendar = Calendar.getInstance();
        //String[] dts = {dt};
        int yr  = calendar.get(Calendar.YEAR);
        int mn  = calendar.get(Calendar.MONTH);
        int dts  = calendar.get(Calendar.DATE);
        String dt = dts+"/"+mn+"/"+yr;
        // check Look and Feel
        background = UIManager.getColor("ComboBox.background");
        foreground = UIManager.getColor("ComboBox.foreground");
        selectedBackground = UIManager.getColor("ComboBox.selectionBackground");
        selectedForeground = UIManager.getColor("ComboBox.selectionForeground");


    // begin ComboPopup method implementations
        public void show() {
        try {
        // if setSelectedItem() was called with a valid date, adjust the calendar
        calendar.setTime( dateFormat.parse( comboBox.getSelectedItem().toString() ) );
        } catch (Exception e) {}
        popup.show(comboBox, 0, comboBox.getHeight());

    public void hide() {

    protected JList list = new JList();
    public JList getList() {
        return list;

    public MouseListener getMouseListener() {
        return this;

    public MouseMotionListener getMouseMotionListener() {
        return this;

    public KeyListener getKeyListener() {
        return this;

    public boolean isVisible() {
        return popup.isVisible();

    public void uninstallingUI() {

    public void mousePressed( MouseEvent e ) {}
        public void mouseReleased( MouseEvent e ) {}
    // something else registered for MousePressed
    public void mouseClicked(MouseEvent e) {
            if ( !SwingUtilities.isLeftMouseButton(e) )
            if ( !comboBox.isEnabled() )
        if ( comboBox.isEditable() ) {
        } else {

    protected boolean mouseInside = false;
    public void mouseEntered(MouseEvent e) {
        mouseInside = true;
    public void mouseExited(MouseEvent e) {
        mouseInside = false;

    // MouseMotionListener
    public void mouseDragged(MouseEvent e) {}
    public void mouseMoved(MouseEvent e) {}

    // KeyListener
    public void keyPressed(KeyEvent e) {}
    public void keyTyped(KeyEvent e) {}
    public void keyReleased( KeyEvent e ) {
        if ( e.getKeyCode() == KeyEvent.VK_SPACE ||
         e.getKeyCode() == KeyEvent.VK_ENTER ) {

     * Variables hideNext and mouseInside are used to
     * hide the popupMenu by clicking the mouse in the JComboBox
    public void popupMenuCanceled(PopupMenuEvent e) {}
    protected boolean hideNext = false;
    public void popupMenuWillBecomeInvisible(PopupMenuEvent e) {
        hideNext = mouseInside;
    public void popupMenuWillBecomeVisible(PopupMenuEvent e) {}

    protected void togglePopup() {
        if ( isVisible() || hideNext ) {
        } else {
        hideNext = false;

    // Note *** did not use JButton because Popup closes when pressed
    protected JLabel createUpdateButton(final int field, final int amount) {
        final JLabel label = new JLabel();
        final Border selectedBorder = new EtchedBorder();
        final Border unselectedBorder = new EmptyBorder(selectedBorder.getBorderInsets(new JLabel()));
        label.addMouseListener(new MouseAdapter() {
            public void mouseReleased(MouseEvent e) {
            calendar.add(field, amount);
            public void mouseEntered(MouseEvent e) {
            public void mouseExited(MouseEvent e) {
        return label;

    protected void initializePopup() {
        JPanel header = new JPanel(); // used Box, but it wasn't Opaque
        header.setLayout(new BoxLayout(header, BoxLayout.X_AXIS));

        JLabel label;
        label = createUpdateButton(Calendar.YEAR, -1);
        label.setToolTipText("Previous Year");


        label = createUpdateButton(Calendar.MONTH, -1);
        label.setToolTipText("Previous Month");

        monthLabel = new JLabel("", JLabel.CENTER);

        label = createUpdateButton(Calendar.MONTH, 1);
        label.setToolTipText("Next Month");

        label = createUpdateButton(Calendar.YEAR, 1);
        label.setToolTipText("Next Year");


        popup = new JPopupMenu();
        popup.setLayout(new BorderLayout());
        popup.add(BorderLayout.NORTH, header);

    // update the Popup when either the month or the year of the calendar has been changed
    protected void updatePopup() {
        monthLabel.setText( monthFormat.format(calendar.getTime()) );
        if (days != null) {
        days = new JPanel(new GridLayout(0, 7));

        Calendar setupCalendar = (Calendar) calendar.clone();
        setupCalendar.set(Calendar.DAY_OF_WEEK, setupCalendar.getFirstDayOfWeek());
        for (int i = 0; i < 7; i++) {
        int dayInt = setupCalendar.get(Calendar.DAY_OF_WEEK);
        JLabel label = new JLabel();
        if (dayInt == Calendar.SUNDAY) {
        } else if (dayInt == Calendar.MONDAY) {
        } else if (dayInt == Calendar.TUESDAY) {
        } else if (dayInt == Calendar.WEDNESDAY) {
        } else if (dayInt == Calendar.THURSDAY) {
        } else if (dayInt == Calendar.FRIDAY) {
        } else if (dayInt == Calendar.SATURDAY){
        setupCalendar.roll(Calendar.DAY_OF_WEEK, true);

        setupCalendar = (Calendar) calendar.clone();
        setupCalendar.set(Calendar.DAY_OF_MONTH, 1);
        int first = setupCalendar.get(Calendar.DAY_OF_WEEK);
        for (int i = 0; i < (first - 1); i++) {
        days.add(new JLabel(""));
        for (int i = 1; i <= setupCalendar.getActualMaximum(Calendar.DAY_OF_MONTH); i++) {
        final int day = i;
        final JLabel label = new JLabel(String.valueOf(day));
        label.addMouseListener(new MouseListener() {
            public void mousePressed(MouseEvent e) {}
            public void mouseClicked(MouseEvent e) {}
            public void mouseReleased(MouseEvent e) {
                calendar.set(Calendar.DAY_OF_MONTH, day);
            public void mouseEntered(MouseEvent e) {
            public void mouseExited(MouseEvent e) {


        popup.add(BorderLayout.CENTER, days);

    public static void main(String args[])
        JFrame f = new JFrame();
        Container c = f.getContentPane();
        c.setLayout(new FlowLayout());
        c.add(new JLabel("Date:"));
        DateComboBox dcb = new DateComboBox();
        f.addWindowListener(new WindowAdapter()
            public void windowClosing(WindowEvent e)
        f.setSize(200, 100);


dynamic graphics (Stars)

import java.awt.*;
import javax.swing.*;
import java.awt.event.*;
import java.util.*;
public class Cells1 extends JFrame
     private DPanel pan = new DPanel();
     private Network net = new Network(pan);
     public Cells1()
         addWindowListener(new WindowAdapter()
             public void windowClosing(WindowEvent ev)
     public class DPanel extends JPanel
         private Graphics2D g2f;
         private Color colors[] = new Color[]
         public DPanel()
         public void paintComponent(Graphics g)
             g2f = (Graphics2D)g;
             Vector cells = net.getCells();
             for (int j=0; j < cells.size(); j++)
             g2f = null;
         public void draw(Cell_Info ci)
             if (g2f == null)
             g2f = (Graphics2D)getGraphics();
             Point p = ci.getPoint();
     public class Network extends Object
         private Vector cells = new Vector();
         public Network(final DPanel pan)
             cells.add(new Cell_Info(20,20));
             cells.add(new Cell_Info(50,120));
             cells.add(new Cell_Info(45,20));
             cells.add(new Cell_Info(90,55));
             cells.add(new Cell_Info(40,110));
             cells.add(new Cell_Info(35,80));
             cells.add(new Cell_Info(95,70));
             cells.add(new Cell_Info(24,40));
             cells.add(new Cell_Info(35,60));
             cells.add(new Cell_Info(80,100));
             cells.add(new Cell_Info(90,90));
             javax.swing.Timer ta = new javax.swing.Timer(200, new ActionListener()
                 public void actionPerformed(ActionEvent e)
                     int v = (int)(Math.random()*cells.size());
                     int c = (int)(Math.random()*10);
                     Cell_Info ci = (Cell_Info)cells.get(v);
         public Vector getCells()
     public class Cell_Info extends Object
         Point point;
         int activ = 0;
         public Cell_Info(int x, int y)
             point = new Point(x,y);
         public Point getPoint()
         public int getAction()
         public void setAction(int a)
             activ = a;
     public static void main (String[] args)
         new Cells1();

SQL Interview Questions and Answers

There is given sql interview questions and answers that has been asked in many companies. For PL/SQL interview questions, visit our next...