Top 60% of records to be found

[wickyd]

Hi folx

I am using DBISAM, so here is a list of unsupported SQL:

ALLOCATE CURSOR (Command)
ALLOCATE DESCRIPTOR (Command)
ALTER DOMAIN (Command)
CASE (Expression)
CHECK (Constraint)
CLOSE (Command)
CONNECT (Command)
CONVERT (Function)
CORRESPONDING BY (Expression)
CREATE ASSERTION (Command)
CREATE CHARACTER SET (Command)
CREATE COLLATION (Command)
CREATE DOMAIN (Command)
CREATE SCHEMA (Command)
CREATE TRANSLATION (Command)
CREATE VIEW (Command)
CROSS JOIN (Relational operator)
DEALLOCATE DESCRIPTOR (Command)
DEALLOCATE PREPARE (Command)
DECLARE CURSOR (Command)
DECLARE LOCAL TEMPORARY TABLE (Command)
DESCRIBE (Command)
DISCONNECT (Command)
DROP ASSERTION (Command)
DROP CHARACTER SET (Command)
DROP COLLATION (Command)
DROP DOMAIN (Command)
DROP SCHEMA (Command)
DROP TRANSLATION (Command)
DROP VIEW (Command)
ESCAPE (LIKE clause)
EXCEPT (Relational operator)
EXECUTE (Command)
EXECUTE IMMEDIATE (Command)
FETCH (Command)
FOREIGN KEY (Constraint)
GET DESCRIPTOR (Command)
GET DIAGNOSTICS (Command)
GRANT (Command)
INTERSECT (Relational operator)
MATCH (Predicate)
NATURAL (Relational operator)
NULLIF (Expression)
OPEN (Command)
OVERLAPS (Predicate)
PREPARE (Command)
REFERENCES (Constraint)
REVOKE (Command)
SET CATALOG (Command)
SET CONNECTION (Command)
SET CONSTRAINTS MODE (Command)
SET DESCRIPTOR (Command)
SET NAMES (Command)
SET SCHEMA (Command)
SET SESSION AUTHORIZATION (Command)
SET TIME ZONE (Command)
SET TRANSACTION (Command)
TRANSLATE (Function)
USING (Relational operator)



I want to find the top 60% of nettsales within an area.



Sample data:

ACCNUM AREA NETTSALES
AAA001 10 134,233.00
AAA034 10 99,230.00
AAA012 10 95,450.00
AAA341 10 80,283.00
AAA234 10 75,530.00
AAA567 10 65,950.00
AAA223 10 45,223.00
AAA665 10 30,030.00
AAA676 10 20,850.00
AAA233 10 18,123.00



I want the result to look like this:

ACCNUM AREA NETTSALES TOP60PERCENT
AAA001 10 134,233.00 1
AAA034 10 99,230.00 1
AAA012 10 95,450.00 1
AAA341 10 80,283.00 1
AAA234 10 75,530.00 1
AAA567 10 65,950.00 1
AAA223 10 45,223.00 0
AAA665 10 30,030.00 0
AAA676 10 20,850.00 0
AAA233 10 18,123.00 0


Thank you.

Kind regards
wickyd

 

[JarlH]

select accnum,
area,
nettsales,
case when (select 1.0 * count(*) from sample
where nettsales < s.nettsales
and area = s.area) /
(select 1.0 * count(*) from sample
where area = s.area) >= 0.4 then 1
else 0 end as TOP60PERCENT
from sample s

ISO/ANSI SQL compliant.

 

[wickyd]

Hi JarlH

Thank you for your response.

Unfortunately, the version of DBISAM I am using does not support CASE.

Here is all the code I use to get to the sample data above:

SELECT ACCNUM, SUM(IF(DOCTYPE = 'I',(TOTAL - TOTALVAT),-(TOTAL - TOTALVAT)))NETTSALES INTO RESULT1 FROM INVOICES
WHERE (DOCTYPE = 'I' OR DOCTYPE = 'C')
AND INVDATE BETWEEN '2002-01-01' AND '2004-12-31'
GROUP BY ACCNUM
ORDER BY NETTSALES DESCENDING;

SELECT ACCNUM, AREA, NETTSALES INTO RESULT2 FROM RESULT1
LEFT OUTER JOIN DEBTORS ON RESULT1.ACCNUM = DEBTORS.ACCOUNT
WHERE AREA = '101';

If I add your code, which I understand as the following:

SELECT ACCNUM, NETTSALES,
       CASE WHEN (SELECT 1.0 * COUNT(*) FROM RESULT2
                  WHERE NETTSALES < S.NETTSALES
                     AND AREA = S.AREA) /
                 (SELECT 1.0 * COUNT(*) FROM RESULT2
                  WHERE AREA = S.AREA) >= 0.4 THEN 1
                  ELSE 0 END AS TOP60PERCENT
FROM RESULT2 S

I get the following error message:
FROM KEYWORD EXPECTED, INSTEAD FOUND '('

Is there another possible solution that does not use CASE?

 

[PHV]

Perhaps this ?

SELECT ACCNUM, NETTSALES,
       IF((SELECT 1.0 * COUNT(*) FROM RESULT2
                  WHERE NETTSALES < S.NETTSALES
                     AND AREA = S.AREA) /
                 (SELECT 1.0 * COUNT(*) FROM RESULT2
                  WHERE AREA = S.AREA) >= 0.4, 1, 0
       ) AS TOP60PERCENT
FROM RESULT2 S

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wickyd]

As I don't understand what you are doing, I am going to give you the error messages as I see them

Current error message:
Right parentheses expected, instead found '1.0' in source column expression

 

[PHV]

And this ?
SELECT ACCNUM, NETTSALES,
IF(((SELECT COUNT(*) FROM RESULT2 WHERE NETTSALES<S.NETTSALES)/(SELECT COUNT(*) FROM RESULT2))>= 0.4, 1, 0) TOP60PERCENT
FROM RESULT2 S

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wickyd]

I really do appreciate your help

Current error message:
Right parentheses expected, instead found 'COUNT' in source column expression


Just to give you an idea:

SELECT COUNT(TOTAL) FROM INVOICES
will give me an answer of X.

SELECT COUNT(0.5 * TOTAL) FROM INVOICES
gives me the SAME answer of X, not half X

 

[PHV]

To get half X:
SELECT 0.5 * COUNT(TOTAL) FROM INVOICES

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wickyd]

Hi PHV

If I try your suggestion, I get the following error message:
Aggregate functions cannot be used within expressions

The joys of using built-in SQL engines.

 

[wickyd]

Hi PHV

Can you please explain in pseudocode how you tackled the problem.

I can then try and modify what you gave me so that I can get to an answer.

Thank you.

 

[PHV]

I just adapted JarlH's suggestion.
Unless DBISAM provides some functions I'm not aware of I don't know how you may solve your issue in pure SQL.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wickyd]

Hi PHV

The following error message:
Aggregate functions cannot be used within expressions

has negated all the effort that was put forward in this thread.

DBISAM has a function called TOP N, but N has to be an integer. I tried TOP with COUNT and CAST but I get the same error:
Aggregate functions cannot be used within expressions

I see no choice but to hard code the values for each area and to do by hand.

Thanks for all the help.

Kind regards
wickyd