INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Count different products

Count different products

(OP)
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

RE: Count different products

CODE

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)

CODE


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
 
 

RE: Count different products




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

CODE

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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Count different products

(OP)
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.

 

RE: Count different products

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 ..cheers )  

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

HTH




CODE

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 winky smile  

For db2 resoruces visit www.db2click.com
More DB2 questions answered at www.dbforums.com/f8 & http://www.idug.org/user/UserLogin.asp

RE: Count different products

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

Ties Blom
 
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close