Jump to content

Subtracting Queries


Recommended Posts

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

Link to post
Share on other sites

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

Link to post
Share on other sites

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;

Link to post
Share on other sites
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

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...