SQL Snippets ™: Tutorials for Oracle Developers

The rules for columns based on constant expressions differ slightly from those for table columns. As with table based columns we can include constant columns in the GROUP BY clause

select 123, 'XYZ', SYSDATE, grp_a, grp_b, count(*)
from   t
group by 123, 'XYZ', SYSDATE, grp_a, grp_b
order by grp_a, grp_b ;
 

       123 'XY SYSDATE    GRP_A  GRP_B    COUNT(*)
---------- --- ---------- ------ ------ ----------
       123 XYZ 2009-06-07 a1     b1              2
       123 XYZ 2009-06-07 a1     b2              3
       123 XYZ 2009-06-07 a2     b3              3
 

and we can GROUP BY constant columns that are not in the SELECT list.

select grp_a, grp_b, count(*)
from   t
group by 123, 'XYZ', SYSDATE, grp_a, grp_b
order by grp_a, grp_b ;
 

GRP_A  GRP_B    COUNT(*)
------ ------ ----------
a1     b1              2
a1     b2              3
a2     b3              3
 

Unlike table based columns we can select constant columns that are absent from the GROUP BY list.

select 123, 'XYZ', SYSDATE, grp_a, grp_b, count(*)
from   t
GROUP BY GRP_A, GRP_B
order by grp_a, grp_b ;
 

       123 'XY SYSDATE    GRP_A  GRP_B    COUNT(*)
---------- --- ---------- ------ ------ ----------
       123 XYZ 2009-06-07 a1     b1              2
       123 XYZ 2009-06-07 a1     b2              3
       123 XYZ 2009-06-07 a2     b3              3
 

Note how all three queries returned the same number of rows.