February 4, 2002 at 9:12 pm
Look at this please
select Total#=count(*) , remsae1 as remsae1_5, sub_remsae1 as sub_remsae1_5,vremsae1 as vremsae1_5
from demographics
where len(remsae1) = 1
group by remsae1,sub_remsae1,vremsae1
union all
select count(*) as Total#, remsae2,sub_remsae2,vremsae2
from demographics
where len(remsae2) = 1
group by remsae2,sub_remsae2,vremsae2
union all
select count(*) as Total#, remsae3,sub_remsae3,vremsae3
from demographics
where len(remsae3) = 1
group by remsae3,sub_remsae3,vremsae3
union all
select count(*) as Total#, remsae4,sub_remsae4,vremsae4
from demographics
where len(remsae4) = 1
group by remsae4,sub_remsae4,vremsae4
union all
select count(*) as Total#, remsae5,sub_remsae5,vremsae5
from demographics
where len(remsae5) = 1
group by remsae5,sub_remsae5,vremsae5
order by remsae1_5,sub_remsae1_5
remsae1,2,3,4,5 can be C, H, D or S , sub_rem* and vrem* are both integers
this gives me something like this
10 C 5 99
2 C 5 99
10 could be from remsae1 and 2 might be from remsae2
But i realy would like to have something like
12 C 5 99
I am not sure how would i combine totals together from different columns?
Also at the end i would like to see the sum of Total# column?
I appreciate any help and advice.
Thanks
February 5, 2002 at 4:25 am
If you are trying to add in when the remsae, sub_remsae and vremsae are the same for a total then this will work for you. Hope this helps.
SELECT SUM([Total#]) AS TOTAL#, remsae1_5, sub_remsae1_5, vremsae1_5 FROM
(
select Total#=count(*) , remsae1 as remsae1_5, sub_remsae1 as sub_remsae1_5,vremsae1 as vremsae1_5
from demographics
where len(remsae1) = 1
group by remsae1,sub_remsae1,vremsae1
union all
select count(*) as Total#, remsae2,sub_remsae2,vremsae2
from demographics
where len(remsae2) = 1
group by remsae2,sub_remsae2,vremsae2
union all
select count(*) as Total#, remsae3,sub_remsae3,vremsae3
from demographics
where len(remsae3) = 1
group by remsae3,sub_remsae3,vremsae3
union all
select count(*) as Total#, remsae4,sub_remsae4,vremsae4
from demographics
where len(remsae4) = 1
group by remsae4,sub_remsae4,vremsae4
union all
select count(*) as Total#, remsae5,sub_remsae5,vremsae5
from demographics
where len(remsae5) = 1
group by remsae5,sub_remsae5,vremsae5
) AS tblBaseResults
GROUP BY remsae1_5, sub_remsae1_5, vremsae1_5
ORDER BY remsae1_5,sub_remsae1_5
February 5, 2002 at 7:18 am
Thanks for your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply