Help with an SQL query

[WYBaugh]

Hi,

I was hoping someone could push me in the right direction for consolidating the following queries into a single pass query. Currently I do the following queries:

db2 &quot;select count(*) as less_equal_100 from household where sptsaccu6 <= 10099&quot; > baby_percent.out
#
db2 &quot;select count(*) as between_101_125 from household where sptsaccu6 >= 10100 and sptsaccu6 <= 12599&quot; >> baby_percent.out
#
db2 &quot;select count(*) as between_126_150 from household where sptsaccu6 >= 12600 and sptsaccu6 <= 15099&quot; >> baby_percent.out
#
db2 &quot;select count(*) as between_151_175 from household where sptsaccu6 >= 15100 and sptsaccu6 <= 17599&quot; >> baby_percent.out

I would like to combine these separate queries into one query that would require only one pass of the household table.

Thank you in advance for the help!

Bill

 

[blom0344]

If you have exclusive conditions, you may want to try a UNION query:

SELECT 'LESS_EQUAL_100',COUNT(*) FROM HOUSEHOLD
WHERE .............
UNION
SELECT ''BETWEEN_101_AND_250', COUNT(*) FROM HOUSEHOLD
WHERE..............
UNION
ETC,ETC


That way you will be able to distinct between the several classes you created...


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

 

[MarcLodge]

Hi Bill,
If you wanted to avoid making multiple passes of the table, you could do it this way, although I admit, it's not the prettiest way. If someone can come up with something better, please post it here. I haven't actually tested it, but I think it should work and may even be syntactilly correct if you're lucky!

select literal, sum(count1) from

(select count(*) as count1,
case
when sptsaccu6 <= 10099 then 'less equal 100'
when sptsaccu6 between 10100 and 12599 then 'between_101_125'
when sptsaccu6 between 12600 and 15099 then 'between_126_150'
when sptsaccu6 between 15100 and 17599 then 'between_151_175'
end as literal
from household
group by sptsaccu6)
as temp_tab
group by literal

Hope this helps.

Marc

 

[MarcLodge]

Bill,
How did you get on with this? Did this solve your problems? A little bit of feedback is always nice.
Marc

 

[WYBaugh]

Marc,

Hi...sorry for taking so long to get back to you. We've been having other database issues that have kept me from running the query.

What you posted ran great and was exactly what I was looking for! Thank you and T. Blom for all of you help!!

Bill