# Thread: SQL Problem when doing percentage

1. Registered User
Join Date
Jul 2003
Location
Wimbledon
Posts
10

## Unanswered: SQL Problem when doing percentage

Hi

I am trying to calculate a percentage using the following SQL :

SELECT
DBP1.VDWMCRD_CREDITS.CUST_ACC_NUM,
DBP1.VDWMCRD_CREDITS.CUST_HOME_BR,
DBP1.VDWMCRD_CREDITS.STMT_YEAR,
DBP1.VDWMCRD_CREDITS.STMT_MONTH,
Sum((DBP1.VDWMCRD_CREDITS.SALES_VALUE+1)*-1) AS CREDIT_VALUE,
Sum(DBP1.VDWMSLS_SALES.SALES_VALUE) AS SLS_VALUE,
DBP1.VDWMBRA_BRANCH.BRANCH_NAME,
DBP1.VDWMCUS_CUST_ACC.CUSTOMER_NAME,
float(float(sum(DBP1.VDWMCRD_CREDITS.SALES_VALUE*-1)+1)
/
float(sum((DBP1.VDWMCRD_CREDITS.SALES_VALUE*-1)+(DBP1.VDWMSLS_SALES.SALES_VALUE)))) AS PERCENT_OF_SLS
FROM
DBP1.VDWMCUS_CUST_ACC INNER JOIN DBP1.VDWMBRA_BRANCH INNER JOIN DBP1.VDWMCRD_CREDITS ON DBP1.VDWMBRA_BRANCH.BRANCH_NUM = DBP1.VDWMCRD_CREDITS.CUST_HOME_BR ON DBP1.VDWMCUS_CUST_ACC.CUST_ACC_NUM = DBP1.VDWMCRD_CREDITS.CUST_ACC_NUM INNER JOIN DBP1.VDWMSLS_SALES ON DBP1.VDWMCRD_CREDITS.CUST_ACC_NUM = DBP1.VDWMSLS_SALES.CUST_ACC_NUM AND DBP1.VDWMCRD_CREDITS.PRODUCT_TYPE = DBP1.VDWMSLS_SALES.PRODUCT_TYPE AND DBP1.VDWMCRD_CREDITS.STMT_YEAR = DBP1.VDWMSLS_SALES.STMT_YEAR AND DBP1.VDWMCRD_CREDITS.STMT_MONTH = DBP1.VDWMSLS_SALES.STMT_MONTH AND DBP1.VDWMCRD_CREDITS.CUST_HOME_BR = DBP1.VDWMSLS_SALES.CUST_HOME_BR
WHERE
DBP1.VDWMCRD_CREDITS.CUST_HOME_BR='101'
AND
DBP1.VDWMCRD_CREDITS.STMT_YEAR=2003
AND
DBP1.VDWMCRD_CREDITS.STMT_MONTH=7
GROUP BY
DBP1.VDWMCRD_CREDITS.CUST_ACC_NUM,
DBP1.VDWMCRD_CREDITS.CUST_HOME_BR,
DBP1.VDWMCRD_CREDITS.STMT_YEAR,
DBP1.VDWMCRD_CREDITS.STMT_MONTH,
DBP1.VDWMBRA_BRANCH.BRANCH_NAME,
DBP1.VDWMCUS_CUST_ACC.CUSTOMER_NAME
HAVING
Sum(DBP1.VDWMCRD_CREDITS.SALES_VALUE) <> 0 AND
Sum(DBP1.VDWMSLS_SALES.SALES_VALUE) <> 0;

We are trying to show the credits as a percentage of the sales value.
The sales value is net of credits, so that is why we are multiplying the credit value (stored as negative) by -1 to make it positive, and then adding it to the sales value to get the gross sales value.
Under the having we have tried to exclude divide by 0s.

However, when the results return we get 0 for all of the percentage values which is definitely not correct.
We believe that when DB2 returns the results it is returning the percentage value as an integer rather than a floating point number.

The underlying reason why we are doing this is to get the Top 15 customers based on the % of credits to sales.

Does anyone know a way around this or can you advise on any problems with above SQL?

Brendan

2. Super Moderator
Join Date
Aug 2001
Location
UK
Posts
4,650

## Re: SQL Problem when doing percentage

Multiply by 100 to get percentage value ..

HTH
Sathyaram

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•