Union

[beechy34]

hi can any one help me i have a table that shows phone calls and one of the colums is called TOAC where it has the area codes of the numbers that have been called to.what i am tring to do is to make a query that shows the total amount of all the special service numbers in one column and then in another column would have the total of all the normal numbers. ive managed to get it to do everything i want but i cant get them to go into different columns they are both in the same one.this is what my code looks like

select * from(select COUNT(TOAC) as "beech"  from TELCO_CALLSwhere TOAC Like '01%'unionselect COUNT(TOAC) As "CallsSpecNumbers"from TELCO_CALLSwhere TOAC not Like '01%');

please can you help methankyou

[justsomeguy]

I think you only need the inner portion.

select COUNT(TOAC) as `beech`  from TELCO_CALLSwhere TOAC Like '01%'unionselect COUNT(TOAC) As `CallsSpecNumbers`from TELCO_CALLSwhere TOAC not Like '01%'

The column names shouldn't go in double quotes, either leave the quotes off or use the backtick (assuming you're working with MySQL).

[beechy34]

ok what you just told me worked but without the alias so it looks like this now and works but not the way i want.

select COUNT(TOAC) from TELCO_CALLSwhere TOAC Like '01%'unionselect COUNT(TOAC) from TELCO_CALLSwhere TOAC not Like '01%';

and here is the outputCOUNT(TOAC) ---------------------- 10 40but this is how i want it to look COUNT(TOAC) COUNT(TOAC) ------------------------------------- 10 40 something like that do you get methankyou

[beechy34]

ok i figured out how to do what i wanted but there is another problem here is the code

select count(a1.TOAC), count(a2.TOAC) from TELCO_CALLS a1, TELCO_CALLS a2where a1.TOAC like '01%' and a2.TOAC not like '01%'order by a1.TOAC;

and this is the outputCOUNT(A1.TOAC) COUNT(A2.TOAC) ---------------------- ---------------------- 400 400 the problem is the number should say 10 and then 40 can any one tell me how to make it work.thankyou

[justsomeguy]

A join does a cartesian product on the table, you're seeing 400 because that's 10 * 40. It's finding all possible combinations where one field starts with '01' and one doesn't, and there are 400 combinations.The union above might be the best option, even though the 2 numbers are in different rows (you at least get the numbers you're looking for). This may work also:

SELECT   (SELECT COUNT(TOAC) FROM TELCO_CALLS WHERE TOAC LIKE '01%') AS num1,  (SELECT COUNT(TOAC) FROM TELCO_CALLS WHERE TOAC NOT LIKE '01%') AS num2

[beechy34]

cheers dude, youve really helped me out there.thankyou