Search code examples
sqldb2ibm-midrange

db2 specific query for count of distinct rows

I am using DB2. I have this query: 

SELECT DISTINCT 
ORDER_NUMBER,
trdm as TRADEMARK,
cmdnsm as CUSTOMER_NAME
FROM DB1.ORDFA
JOIN DB2.CCADF ON SFORD# = WDRSO
JOIN DB2.DADA06 ON WD3ADF = WD3FLA
JOIN DB1.CMP1 ON C1LK2 = SF2LK
WHERE YEAR = '14' AND PART = '5484'
ORDER BY CUSTOMER_NAME, TRADEMARK

and I get this result set from the above query

order_number    trademark      customer_name
  2220824.        AIR          EPR PRODUCTS INC
  2254614.       AVALON        EPR PRODUCTS INC
  2214146.       AVALON        EPR PRODUCTS INC
  2338002.       AVALON        EPR PRODUCTS INC
  2102733.       BOSS          EPR PRODUCTS INC
  2286148.        CDC          EPR PRODUCTS INC
  2116148.        CDC          EPR PRODUCTS INC
  2032105.        HC           SUMMLC
  2014046.        HC           SUMMLC
  2232312.       SDEXO         SUMMLC
  2267369.       SDEXO         SUMMLC
  2085280.       SDEXO         SUMMLC
  2170433.       SDEXO         SUMMLC
  2179283.       SDEXO         SUMMLC
  2378745.       SDEXO         SUMMLC

What I need is a DB2 query that would give me this result set: 

 NUM_ORDERS      TRADEMARK      CUSTOMER_NAME
     6            SDEXO          SUMMLC
     3            AVALON         EPR PRODUCTS INC
     2            CDC            EPR PRODUCTS INC
     2            HC             SUMMLC
     1            BOSS           EPR PRODUCTS INC
     1            AIR            EPR PRODUCTS INC
Solution

  • If DB2 supports ansi standard sql I believe all you need is to add acountandgroup by:

    SELECT 
    COUNT(DISTINCT ORDER_NUMBER) AS NUM_ORDERS,
    trdm as TRADEMARK,
    cmdnsm as CUSTOMER_NAME
FROM DB1.ORDFA
JOIN DB2.CCADF ON SFORD# = WDRSO
JOIN DB2.DADA06 ON WD3ADF = WD3FLA
JOIN DB1.CMP1 ON C1LK2 = SF2LK
WHERE YEAR = '14' AND PART = '5484'
GROUP BY trdm, cmdnsm
ORDER BY NUM_ORDERS DESC, CUSTOMER_NAME, TRADEMARK