Help with SQL query.

[manisri]

Hi All,
I am new to this area and would really appreciate ur help with this query.
I have to get count of all (valid and invalid) phone nos. from a table ABC.

ABC has columns city,phone_number, time_key.....

The output format is like this:

city Valid Invalid
________________________
chicago 123 345
lincoln 873 121
...
....
....

I am give following conditions to decide on valid and invalid phone number.

Range of Valid and invalid numbers
_____________________________________________

Chicago 1000000 through 1900000 -> valid
1900001 through 2100000 -> invalid

lincoln 2000000 through 2999999 -> valid
3000000 through 3999999 -> invalid

I guess we would have to use case statment...

Thanks in advance,
Mani.

 

[blom0344]

Mixing Case with UNION , hope you have a few cities:

(SELECT CITY,
SUM(CASE WHEN PHONE BETWEEN 1000000 AND 1900000 THEN 1 ELSE 0 END) AS VALID,
SUM(CASE WHEN PHONE BETWEEN 1900001 AND 2100000 THEN 1 ELSE 0 END) AS INVALID
FROM CITY_PHONE WHERE CITY = 'Chicago'
GROUP BY CITY)
UNION
(SELECT CITY,
SUM(CASE WHEN PHONE BETWEEN 2000000 AND 2999999 THEN 1 ELSE 0 END) AS VALID,
SUM(CASE WHEN PHONE BETWEEN 3000000 AND 3999999 THEN 1 ELSE 0 END) AS INVALID
FROM CITY_PHONE WHERE CITY = 'Lincoln'
GROUP BY CITY)

T. Blom
Information analyst
tbl@shimano-eu.com

 

[sathyarams]

Just curious ..
Can this line be :
FROM CITY_PHONE WHERE CITY in ('Chicago','Lincoln')

avoiding UNION ?

Cheers

SAthyaram

More DB2 questions answered at

http://www.dbforums.com/f8

 

[blom0344]

Nope,

At least I think that with every city having it's own conditions, you cannot work with the in list method.
You need to split at city level.

Or am I missing the obvious?



T. Blom
Information analyst
tbl@shimano-eu.com

 

[manisri]

Thanks Blom and Sathyaram. I appreciate ur help...

-Mani

 

[sathyarams]

Sorry Blom ... You were absolutely right ... I just realize it now ...

Cheers
Sathyaram

More DB2 questions answered at

http://www.dbforums.com/f8

 

[MarcLodge]

Hi All,
I tink the following should work and will avoid the overhead of a UNION. Will need to add extra clauses for validation of each city:

SELECT CITY,
SUM(CASE
WHEN (CITY = 'Chicago' AND PHONE BETWEEN 1000000 AND 1900000)
OR (CITY = 'Lincoln' AND PHONE BETWEEN 2000000 AND 2999999 )
THEN 1
ELSE 0
END)
,SUM(CASE
WHEN (CITY = 'Chicago' AND PHONE BETWEEN 1900001 AND 2100000 )
OR (CITY = 'Lincoln' AND PHONE BETWEEN 3000000 AND 3999999 )
THEN 1
ELSE 0
END)
FROM ABC
GROUP BY CITY

 

[blom0344]

Yep,

I had a feeling that spending a bit more time one it would come up with a UNION-less solution.
Marc's approach is probably optimal......

T. Blom
Information analyst
tbl@shimano-eu.com