get different result from 1 column in 1 query

[kinkin83]

Dear All,

i would like to get different result from 1 column in 1 query. the details result is below

database name: Data
table name: table1
column: id, phoneno, timeframe, mo

if MO=1 means MO else MO=0 means MT

my query:

use data
select phoneno, sum(mo) as mo from table1 where mo='1' and mo='0' and timeframe>'20080110' and timeframe<'20080111'

my result:

i get the mo result is 0

expecting result:

1st column is phoneno, 2nd is mo where mo=1, 3rd is mt where mo=0

very much apreciate if you could provide me the right query.

Many Thanks

warmest regards,
Nicholas

[ck9663]

Dear All,

i would like to get different result from 1 column in 1 query. the details result is below

database name: Data
table name: table1
column: id, phoneno, timeframe, mo

if MO=1 means MO else MO=0 means MT

my query:

use data
select phoneno, sum(mo) as mo from table1 where mo='1' and mo='0' and timeframe>'20080110' and timeframe<'20080111'

my result:

i get the mo result is 0

expecting result:

1st column is phoneno, 2nd is mo where mo=1, 3rd is mt where mo=0

very much apreciate if you could provide me the right query.

Many Thanks

warmest regards,
Nicholas

I think you're looking for CASE Function

-- CK

[kinkin83]

Dear CK,

i have searched the CASE function but i dun really know how to use it. could u demostrate for me?

Many Thanks

Regards,
Nicholas

[ck9663]

Would you mind posting some sample data of your table and your desired result?

-- CK

[kinkin83]

Dear CK,

the below is my coding using CASE but the result aint what i wanted. i have changed my column name to.

use data
select mp,mu=
case
when mu='1' then '1'
when mu='0' then '0'
else 'this is wrong'
end
from stats where phone='0122564585'

the table information is as below

note: if the mu=1 means mu, if mu=0 means mp

id
1
1
1
1

Timeframe
20080112
20080112
20080112
20080113

mu
1
0
1
0

Phoneno
0121234567
0121234567
0121234567
0121234567


desire result
the ID comes first follow by all MU (in MU column) then MP (in MP column) then phoneno
ID MU MP Phoneno

Many Thanks

warmest regards,
Nicholas

[ck9663]

You have these columns in 4 tables? How is one related with the others?

-- CK

[kinkin83]

Dear CK,

this is 1 table with all t hese 4 column.

[ck9663]

Dear CK,

the below is my coding using CASE but the result aint what i wanted. i have changed my column name to.

use data
select mp,mu=
case
when mu='1' then '1'
when mu='0' then '0'
else 'this is wrong'
end
from stats where phone='0122564585'

the table information is as below

note: if the mu=1 means mu, if mu=0 means mp

id
1
1
1
1

Timeframe
20080112
20080112
20080112
20080113

mu
1
0
1
0

Phoneno
0121234567
0121234567
0121234567
0121234567


desire result
the ID comes first follow by all MU (in MU column) then MP (in MP column) then phoneno
ID MU MP Phoneno

Many Thanks

warmest regards,
Nicholas

Try:

select ID, MU = case when mu = 1 then 1 else 0 end, 
MP = case when mu = 0 then 1 else 0 end,  Phoneno 
from stats
from stats where phone='0122564585'

Then you can sum the MUs and the MPs to get the count. One of the catch is if there's a value in MU other than 1 and 0, you won't see it. Which means if you count it MP + MU <> TotalRecords.

Looking at your query, it looks like there are multiple records on your table? If your WHERE returns only 1 record, what's the use of separating the MP and MU, since if not MP it's MU and vice versa?

Anyway, good luck. Post here for update and if you need more help.

-- CK

[kinkin83]

Dear CK,

sorry bout the miswritting. it is only one table with all these columns.

this is the result of ur code:
ID MU MP Phoneno
322 1 0 0122564585
323 0 1 0122564585
324 1 0 0122564585
325 0 1 0122564585
326 1 0 0122564585
327 0 1 0122564585
328 1 0 0122564585
329 0 1 0122564585
330 1 0 0122564585
331 0 1 0122564585
332 1 0 0122564585
333 0 1 0122564585
334 1 0 0122564585
335 0 1 0122564585
336 1 0 0122564585
337 0 1 0122564585
338 1 0 0122564585
339 0 1 0122564585
340 1 0 0122564585
341 0 1 0122564585
342 1 0 0122564585
343 0 1 0122564585
344 1 0 0122564585
345 0 1 0122564585
346 1 0 0122564585
347 0 1 0122564585
348 1 0 0122564585
349 0 1 0122564585
463 1 0 0122564585
540 1 0 0122564585
541 0 1 0122564585
542 1 0 0122564585
543 0 1 0122564585
544 1 0 0122564585
545 0 1 0122564585
546 1 0 0122564585
547 0 1 0122564585
548 1 0 0122564585
549 0 1 0122564585
550 1 0 0122564585
551 0 1 0122564585
552 1 0 0122564585
553 0 1 0122564585
554 1 0 0122564585
555 0 1 0122564585
556 1 0 0122564585
557 0 1 0122564585
558 1 0 0122564585
559 0 1 0122564585
560 1 0 0122564585
561 0 1 0122564585
562 1 0 0122564585
563 0 1 0122564585
564 1 0 0122564585

the total number for MU(1) is 28 and MP(0) is also 28. when i count on MU is correct 28 but MP count is 26 only.

could Count works with Case?

please advice...many Thanks

regards,
Nicholas

[ck9663]

Run this:

select MU, count(*)
from stats where phone='0122564585'
 

What are the values of MU ?

-- CK

[kinkin83]

Dear CK,

i use the below code to generate the total MU

select count(mu) from stats where phoneno='0122564585'

total MU is 54

[ck9663]

Try this:

  select ID, MU, MUFlag = case when mu = 1 then 1 else 0 end,
  MPFlag = case when mu = 0 then 1 else 0 end,  Phoneno
  from stats
  from stats where phone='0122564585'

Visually check the MU and if it's properly identified as MU or MP

-- CK

[kinkin83]

Dear CK,

the result of MU and MP is correct. can i use the count (MU) and count (MP) on the above code?

many thanks

regards,
Nicholas

[ck9663]

Dear CK,

the result of MU and MP is correct. can i use the count (MU) and count (MP) on the above code?

many thanks

regards,
Nicholas

Yes, you can use that. It's actually the same query. I just added MU.

-- CK

[kinkin83]

Dear CK,

where do i add the count? i need to count the MU and MP. when i add the count function, it has error.

[ck9663]

try:

select ID, MU, MUFlag = sum(case when mu = 1 then 1 else 0 end),
MPFlag = sum(case when mu = 0 then 1 else 0 end)
from stats
from stats where phone='0122564585'
group by ID, MU

-- CK

[kinkin83]

Dear CK,

your my MAN!!! where do u usually learn from?

very appreciate ur help!!!

Thank you!!!

regards,
Nicholas