Q

Nested GROUP BYs

I've got a query that consolidates a group of rows and gives me a column SUM.

SELECT DISTINCT UNIV_CUST_ID
               ,VEH_APPL_CD
               ,VEH_CTR_CD
               ,SUM (NET_INTL_CNT)  AS NETINTLCNT
  FROM table1
WHERE UNIV_CUST_ID = '0000418749'
GROUP BY UNIV_CUST_ID
        ,VEH_APPL_CD
        ,VEH_CTR_CD
ORDER BY UNIV_CUST_ID
        ,VEH_CTR_CD

The results look like this --

---------+---------+---------+---------+---------+
UNIV_CUST_ID  VEH_APPL_CD  VEH_CTR_CD   NETINTLCNT
---------+---------+---------+---------+---------+
0000418749    527000       HVY                   2
0000418749    535000       HVY                 239
0000418749    611000       HVY                  25
0000418749    612000       HVY                 471
0000418749    612001       HVY                   1
0000418749    613000       HVY                 396
0000418749    530000       MED                   3
0000418749    532000       MED                  59
0000418749    535000       MED                6404
0000418749    535006       MED                  26
0000418749    537000       MED                  64
0000418749    602000       MED                  21
0000418749    603000       MED                  10

How can I have this query return just the MAX amount out of the SUM(NETINTLCNT) values grouped by UNIV_CUST_ID and VEH_CTR_CD? Can I have multiple GROUP BYs in a query, or a subselect GROUP BY?


Use a derived table. In effect, yes, you are doing the GROUP BY in a subquery.

select UNIV_CUST_ID
     , VEH_CTR_CD
     , max(NETINTLCNT) as maxNETINTLCNT 
  from (              
       SELECT UNIV_CUST_ID
            , VEH_APPL_CD
            , VEH_CTR_CD
            , SUM(NET_INTL_CNT) AS NETINTLCNT
         FROM table1
        WHERE UNIV_CUST_ID = '0000418749'
       GROUP
           BY UNIV_CUST_ID
            , VEH_APPL_CD
            , VEH_CTR_CD
       ) as Dtable
group
    by UNIV_CUST_ID
     , VEH_CTR_CD
order
    by UNIV_CUST_ID
     , VEH_CTR_CD

The subquery, inside the parentheses, is a derived table. As a matter of habit, I usually give it an alias of "Dtable" or "DerivedTable" or something, because an alias is required if the derived table is being joined to another table (or even if it isn't, in at least one database system I know). The result will look like this:

---------+---------+---------+---------
UNIV_CUST_ID  VEH_CTR_CD  maxNETINTLCNT
---------+---------+---------+---------
0000418749    HVY                   471
0000418749    MED                  6404

A couple of tips:

  1. Do not use DISTINCT at the same time as GROUP BY. The groups produced by GROUP BY are, by definition, distinct.

  2. Do not use ORDER BY in a derived table. It's not necessary, and it can definitely hinder performance.


This was first published in August 2003

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close