Count different products

[mcauliff]

I'm not sure if this is able to be coded. Using DB2 v8 on IBM Z/OS executing in v7 mode.

I have 19 unique products that a customer may place a call for. I need to count the number of calls for each product by customer.

Is this possible?

Here is a example

Customer 1 ABC Corp has 5 calls for product 1, 3 call for product 2 and 1 call for product 3

The results I'm attempting to get is as follows;
Cust Name Cust No P1 P2 P3
ABC Corp 1 5 3 1

 

[blom0344]

SELECT COUNT(CALL),CUST_NAME,CUST,PRODUCT
FROM TABLE
GROUP BY CUST_NAME,CUST,PRODUCT

YIELDS:

5 ABC Corp 1 P1
3 ABC Corp 1 P2
1 ABC Corp 1 P3

If you need to flatten this into the output you have , then you either use a reporting tool

or write an extensive piece of SQL: (example for 3 products)

SELECT TEMP.A,TEMP.B,TEMP.C,SUM(TEMP.P1),SUM(TEMP.P2),SUM(TEMP.P3) FROM
((SELECT CUST_NAME AS A,CUST AS B,PRODUCT AS C,COUNT(CALLS) AS P1,0 AS P2,0 AS P3
FROM TABLE WHERE PRODUCT = 'P1'
GROUP BY CUST_NAME,CUST,PRODUCT)
UNION
(SELECT CUST_NAME AS A,CUST AS B,PRODUCT AS C,0 AS P1,COUNT(CALLS) AS P2,0 AS P3
FROM TABLE WHERE PRODUCT = 'P2'
GROUP BY CUST_NAME,CUST,PRODUCT)
UNION
(SELECT CUST_NAME AS A,CUST AS B,PRODUCT AS C,0 AS P1,0 AS P2,COUNT(CALLS) AS P3
FROM TABLE WHERE PRODUCT = 'P3'
GROUP BY CUST_NAME,CUST,PRODUCT)) TEMP
GROUP BY TEMP.A,TEMP.B,TEMP.C

Ties Blom

 

[SkipVought]

Hi,

Since you CURRENTLY have 19 products or not, I'd run a report from Excel.

You can configure a DB2 driver and access the database directly using Data > Get External Data > New Databae Query... Once you have this QueryTable inserted into an Excel sheet, all you have to do to get new data is a refresh.

Once you have retrieved the data from DB2, using SQL like

SELECT COUNT(CALL),CUST_NAME,CUST,PRODUCT
FROM TABLE
GROUP BY CUST_NAME,CUST,PRODUCT

as posted above, you can use the PivotTable Wizard, for instance, to report the data in the format you initially specified. This would take less than one minute to do.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[mcauliff]

BLOM0344 and SkipVought

Thank you for the reply. I had a mental block and just couldn't get pass it.

I used the first example from BLOM0344 and SkipVought suggestion to use a Excel Pivot Table.

 

[sathyarams]

This may not be relevant for the OP as Common Table expressions were not available in V7.

The query below generates a csv report giving similar output values as Ties Blom's 'extensive piece of SQL: (example for 3 products)' ... The advantage here being, it can handle (theoretically) unlimited number of products without any change to the core query .

Inline comments hopefully helps (excuse typos in the comments - its a Friday afternoon before a long weekend .. )

For ease, I have used temp tables TABLE and prdlist, which will be permanent tables in real life.

HTH

WITH TABLE
     (
          cust_name,
          cust     ,
          product
     ) AS --- a permanent table . list of calls by customer and prodcut(txn table)
     (
          VALUES
          (
               'ABC Corp',
               1         ,
               'P1'
          )
          ,
          ( 'ABC Corp' , 1 , 'P2'),
          ( 'ABC Corp' , 1 , 'P2'),
          ( 'ABC Corp' , 1, 'P3') ,
          ( 'ABC Corp' , 1, 'P3') ,
          ( 'XYZ Corp', 2, 'P1')  ,
          ( 'XYZ Corp' , 2, 'P3') ,
          ( 'XYZ Corp' , 2, 'P3')
     )
     ,
     prdlist
     (
          prdcd
     ) AS --- permanent table  with a list of prod codes
     (
          VALUES
          (
               'P1'
          )
          ,
          ('P2'),
          ('P3'),
          ('P4')
     )
     ,
     prdlist_seq
     (
          prdcd,
          seqno
     ) AS
     --- gives the sequence in which product columns will be generated .
     -- a permanent config table or can be based on a rule
     --  in this example, the sequence is  alphabetically ordered by the product name
     ( SELECT  prdcd,
              rownumber() over (ORDER BY prdcd)
     FROM     prdlist
     )
     ,
     prdcallct1
     ( -- counts the  number of calls grouped by custname, custid and product
          CALLCT,
          CNAME ,
          CUSTID,
          PRD   ,
          seqno
     ) AS
     ( SELECT  COUNT(*),
              CUST_NAME,
              CUST     ,
              PRoduct  ,
              -100
     FROM     TABLE
     GROUP BY CUST_NAME,
              CUST     ,
              PRoduct
     )
     ,
     prdcallct2
     (
          callct,
          CNAME ,
          CUSTID,
          PRDcd ,
          seqno
     ) AS
     -- 'inserts' 0 count from procuts  'missing'  for a customer
     -- there should be a more efficient way of doing prdcallct2
     --
     ( SELECT DISTINCT 0                     ,
                                       cname ,
                                       custid,
                                       prdcd ,
                                       ps.seqno
                      FROM             prdlist_seq ps ,
                                       prdcallct1
     )
     ,
     prdcallct
     (
          callct,
          CNAME ,
          CUSTID,
          PRDcd ,
          seqno
     ) AS -- counts the number of calls by customer by product
     ( SELECT  SUM(callct),
              cname       ,
              custid      ,
              prdcd       ,
              MAX(seqno)
     FROM
              ( SELECT *
              FROM    prdcallct1
              
              UNION ALL
              
              SELECT *
              FROM   prdcallct2
              ) AS x(callct,CNAME,CUSTID, PRDcd,seqno)
     GROUP BY cname ,
              custid,
              prdcd
     )
     ,
     temp
     (
          seq   ,
          CNAME ,
          CUSTID,
          prdcd ,
          callctlist
     ) AS -- generate the  call count list for products by customer
     ( SELECT seqno,
             cname ,
             custid,
             prdcd ,
             CAST(rtrim(CHAR(callct)) AS VARCHAR(1000))
     FROM    prdcallct
     WHERE   seqno=1
     
     UNION ALL
     
     SELECT seq+1   ,
            t.cname ,
            t.custid,
            t.prdcd ,
            t.callctlist
                   ||','
                   ||rtrim(CHAR(callct))
     FROM   temp t,
            prdcallct p
     WHERE  seqno   =seq+1
        AND t.custid=p.custid
     )
     ,
     headerrow
     (
          s,
          x
     ) AS -- generate header row
     ( SELECT seqno,
             CAST(rtrim(prdcd) AS VARCHAR(1000))
     FROM    prdlist_seq
     WHERE   seqno=1
     
     UNION ALL
     
     SELECT s+1,
            x
                   ||','
                   ||rtrim(prdcd)
     FROM   headerrow h,
            prdlist_seq
     WHERE  s+1=seqno
     )
     ,
     finalresult
     (
          ord,
          value
     ) AS --- print the result
     ( SELECT -100,
             'Custid,custname,prdcd,'
                     ||x
     FROM    headerrow
     WHERE   s=
             (SELECT MAX(s)
             FROM    headerrow
             )
     
     UNION ALL
     
     SELECT 100,
            rtrim(CHAR(Custid))
                   ||','
                   ||rtrim(Cname)
                   ||','
                   ||rtrim(callctlist)
     FROM   temp
     WHERE
            (
                   custid,seq
            )
            IN
            (SELECT  custid,
                     MAX( seq)
            FROM     temp
            GROUP BY custid
            )
     )
SELECT   value
FROM     finalresult
ORDER BY ord

PS: Ties, Thanks I have used you example and table names ;-)

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[blom0344]

Bloody awesome solution I'd say. Just wished I had a DB2 instance running..

Ties Blom