Subtracting Queries

[beechy34]

this is my code

SELECT *from  (SELECT COUNT(TOAC)as "callsNormNumbers" FROM TELCO_CALLS  WHERE TOAC LIKE '01%') -  (SELECT COUNT(TOAC)as "callsNormNumbers" FROM TELCO_CALLS  WHERE TOAC not LIKE '01%');

this code is supposed to take away the total of all numbers that start with 01 from the TOAC column from the total of all the numbers that do not start with 01 from the same coumn, but it doesnt seem to work can someone help methankyou

[justsomeguy]

SELECT ((SELECT COUNT(TOAC) FROM TELCO_CALLS WHERE TOAC LIKE '01%') - (SELECT COUNT(TOAC) FROM TELCO_CALLS WHERE TOAC not LIKE '01%')) AS num

[beechy34]

ok i tried it and had to change it a little to make give me an out put this is the code

SELECT((SELECT COUNT(TOAC) FROM TELCO_CALLS WHERE TOAC LIKE '01%') -(SELECT COUNT(TOAC) FROM TELCO_CALLS WHERE TOAC not LIKE '01%')) as beechfrom TELCO_CALLS;

the problem now is that it gives me 50 rows with each row showing the value of 30, all i want it 1 row showing the value of 30.can you or someone helpthankyou

[Synook]

Try

SELECT DISTINCT ((SELECT COUNT(TOAC) FROM TELCO_CALLS WHERE TOAC LIKE '01%') -(SELECT COUNT(TOAC) FROM TELCO_CALLS WHERE TOAC not LIKE '01%')) as beechfrom TELCO_CALLS;

or if only one distinct value will ever be returned just

SELECT((SELECT COUNT(TOAC) FROM TELCO_CALLS WHERE TOAC LIKE '01%') -(SELECT COUNT(TOAC) FROM TELCO_CALLS WHERE TOAC not LIKE '01%')) as beechfrom TELCO_CALLS LIMIT 1;

[Stefano]

SELECT ((SELECT COUNT(TOAC) FROM TELCO_CALLS WHERE TOAC LIKE '01%') - (SELECT COUNT(TOAC) FROM TELCO_CALLS WHERE TOAC not LIKE '01%')) AS num

As for me is not a count issue!!!The following works with mysql, if you use oracle use "decode" function in stead of "if()" function, anything else let me know

SELECT sum( num )FROM (SELECT if( TOAC LIKE '01%', +1, -1 ) AS numFROM TELCO_CALLS) AS aname

regardsstefano De Boni