By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,400 Members | 1,335 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,400 IT Pros & Developers. It's quick & easy.

get different result from 1 column in 1 query

P: 9
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
Feb 22 '08 #1
Share this Question
Share on Google+
16 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Feb 22 '08 #2

P: 9
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
Feb 26 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Would you mind posting some sample data of your table and your desired result?

-- CK
Feb 26 '08 #4

P: 9
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
Feb 27 '08 #5

ck9663
Expert 2.5K+
P: 2,878
You have these columns in 4 tables? How is one related with the others?

-- CK
Feb 27 '08 #6

P: 9
Dear CK,

this is 1 table with all t hese 4 column.
Feb 27 '08 #7

ck9663
Expert 2.5K+
P: 2,878
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:

Expand|Select|Wrap|Line Numbers
  1. select ID, MU = case when mu = 1 then 1 else 0 end, 
  2. MP = case when mu = 0 then 1 else 0 end,  Phoneno 
  3. from stats
  4. 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
Feb 27 '08 #8

P: 9
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
Feb 28 '08 #9

ck9663
Expert 2.5K+
P: 2,878
Run this:

Expand|Select|Wrap|Line Numbers
  1. select MU, count(*)
  2. from stats where phone='0122564585'
  3.  
What are the values of MU ?

-- CK
Feb 28 '08 #10

P: 9
Dear CK,

i use the below code to generate the total MU

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

total MU is 54
Feb 28 '08 #11

ck9663
Expert 2.5K+
P: 2,878
Try this:

Expand|Select|Wrap|Line Numbers
  1.   select ID, MU, MUFlag = case when mu = 1 then 1 else 0 end,
  2.   MPFlag = case when mu = 0 then 1 else 0 end,  Phoneno
  3.   from stats
  4.   from stats where phone='0122564585'
Visually check the MU and if it's properly identified as MU or MP

-- CK
Feb 28 '08 #12

P: 9
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
Feb 29 '08 #13

ck9663
Expert 2.5K+
P: 2,878
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
Feb 29 '08 #14

P: 9
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.
Feb 29 '08 #15

ck9663
Expert 2.5K+
P: 2,878
try:

Expand|Select|Wrap|Line Numbers
  1. select ID, MU, MUFlag = sum(case when mu = 1 then 1 else 0 end),
  2. MPFlag = sum(case when mu = 0 then 1 else 0 end)
  3. from stats
  4. from stats where phone='0122564585'
  5. group by ID, MU
-- CK
Feb 29 '08 #16

P: 9
Dear CK,

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

very appreciate ur help!!!

Thank you!!!

regards,
Nicholas
Feb 29 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.