Jump to content

Union


beechy34
 Share

Recommended Posts

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

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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

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

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...