Re: CrossTab Queries Groupings

Do you mean view?

Use "select from select" and aggregate more in the outer select:

select Code, Type, sum(call_resolution)
from your_view
group by Code, Type

"Vincent Jones" <vncntj_at_hotmail.com> wrote in message news:ba2a2d30.0304161034.326ebc6c_at_posting.google.com...
> I have this sp
> Alter Procedure ps_data_bak
> AS
> Select Distinct
> CASE
> When Substring(o.Appeal_Code,4,1) = 'U' Then 'Membership -
> Individual and Household'
> When Substring(o.Appeal_Code,4,1) = 'H' Then 'Membership -
> Individual and Household'
> When Substring(o.Appeal_Code,4,1) = 'I' Then 'Membership -
> Individual and Household'
> When Substring(o.Appeal_Code,4,1) = 'A' Then 'Membership -
> Contributor and Supporter'
> When Substring(o.Appeal_Code,4,1) = 'P' Then 'Membership -
> Contributor and Supporter'
> When Substring(o.Appeal_Code,4,1) = 'B' Then 'Membership -
> Contributor and Supporter'
> When Substring(o.Appeal_Code,4,1) = 'C' Then 'Development -
> Sustainer, Sponsor and Patron'
> When Substring(o.Appeal_Code,4,1) = 'D' Then 'Development -
> Sustainer, Sponsor and Patron'
> When Substring(o.Appeal_Code,4,1) = 'E' Then 'Development -
> Sustainer, Sponsor and Patron'
> When Substring(o.Appeal_Code,4,1) = 'F' Then 'Development Level 2'
> END AS Code,
> CASE
> When Substring(o.Appeal_Code,2,1) = 'D' Then 'Upgrade'
> When Substring(o.Appeal_Code,2,1) = 'R' Then 'Renewal'
> When Substring(o.Appeal_Code,2,1) = 'L' Then 'Lapse'
> End As Type,
> Sum(o.Call_Resolution) 'Call Resolution'
> From tblLead As o
> Group By Substring(o.Appeal_Code,4,1), Substring(o.Appeal_Code,2,1)
> Having Substring(o.Appeal_Code,2,1) Is Not Null
> Order By Code, Type
>
> But I still get this output:
>
> Code Type Call Resolution
> Development - Sustainer, Sponsor and Patron Lapse 6
> Development - Sustainer, Sponsor and Patron Lapse 23
> Development - Sustainer, Sponsor and Patron Lapse 187
> Development - Sustainer, Sponsor and Patron Renewal 83
> Development - Sustainer, Sponsor and Patron Renewal 289
> Development - Sustainer, Sponsor and Patron Renewal 1107
> Development - Sustainer, Sponsor and Patron Upgrade 22
> Development - Sustainer, Sponsor and Patron Upgrade 73
> Development - Sustainer, Sponsor and Patron Upgrade 265
> Development Level 2 Lapse 12
> Development Level 2 Renewal 44
> Membership - Contributor and Supporter Lapse 240
> Membership - Contributor and Supporter Lapse 558
> Membership - Contributor and Supporter Renewal 1439
> Membership - Contributor and Supporter Renewal 2647
> Membership - Contributor and Supporter Upgrade 590
> Membership - Contributor and Supporter Upgrade 1029
> Membership - Individual and Household Lapse 1311
> Membership - Individual and Household Lapse 2210
> Membership - Individual and Household Renewal 2669
> Membership - Individual and Household Renewal 5506
> Membership - Individual and Household Upgrade 4
> Membership - Individual and Household Upgrade 3570
> Membership - Individual and Household Upgrade 6291
>
> I just want to see
> Development - Sustainer, Sponsor and Patron Lapse 216
> Development - Sustainer, Sponsor and Patron Renewal 1479
>
> Thanks.
Received on Wed Apr 16 2003 - 15:18:09 CDT