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:
Do not use DISTINCT at the same time as GROUP BY. The groups produced by GROUP BY are, by definition, distinct.
Do not use ORDER BY in a derived table. It's not necessary, and it can definitely hinder performance.