Crosstab Help

[prgmrgirl]

Hey everyone, this one is just making me crazy.

I have a table (custSales) with the following columns:

custType, Period, Sales

Now I would like to create a crosstab, like so:

Select custType,
Sum(case Period when 'TY' then Sales else null end) As ThisYear,
Sum(case Period when 'LY' then Sales else null end) As LastYear
From custSales
Group By custType

This works...sort of...I get the values I want but like this:

custType ThisYear Lastyear
Bookstore NULL 523.14
Bookstore 847.35 NULL

In other words, it gives me a separate line for each one instead of putting on the same line like this:

custType ThisYear LastYear
Bookstore 847.35 523.14

Can anyone see what's wrong with my statement? What am I doing wrong?

Thanks a bunch!
prgmrgirl

 

[gmmastros]

Select custType,
[!]Min([/!]Sum(case Period when 'TY' then Sales else null end)[!])[/!] As ThisYear,
[!]Min([/!]Sum(case Period when 'LY' then Sales else null end)[!])[/!] As LastYear
From  custSales
Group By custType

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[prgmrgirl]

Thanks for your response, George!

I got an error when I tried to use Min. However, I think I see what happened.

I actually had 2 columns I was trying to cross on, like this:

Location, CustType, Period, Sales

I found that if I removed Location, then the crosstab worked fine. When I added Location again afterwards, it worked.

Must be a small bug when you try to use two crosses.

As an example, this is what I wanted to achieve:

Location CustType ThisYear LastYear
Chi Bookstore 847.35 523.14
Chi Drugstore 412.35 400.15

And so on...

In order to get it, I had to run it first with just the CustType, then add Location and run it again. Then it worked.

Hope this helps someone out there!

 

[gmmastros]

And this didn't work for you?

Select Location,
       custType,
       Min(Sum(case Period when 'TY' then Sales else null end)) As ThisYear,
       Min(Sum(case Period when 'LY' then Sales else null end)) As LastYear
From   custSales
Group By Location, custType
Order By Location, custType

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[prgmrgirl]

Nope, errored on Min.