Displaying SELECT Query Results - from Same Table

问题:

I've got an SQL query that I just can't get to display the way I want it to. Basically, we've got a table full of transactions and I'd like to get a sum (of a particular type of transaction ) purchased from February of this year until now. Here's the query for that:

select sum(amount) as "2011" from transactions
where transaction_type = 'registration'
and date_entered > '2011-02-01'
and date_entered < GetDate()

Now, I'd also like to see a sum of those same transactions but for the previous year:

select sum(amount) as "2010" from transactions
where transaction_type = 'registration'
and date_entered > '2010-02-01'
and date_entered < DateAdd(yy, -1, GetDate())

What I can't seem to figure out is how to get these sums side-by-side. I've tried UNION all but those display in separate rows, not columns.

select sum(amount) as "2011" from transactions
where transaction_type = 'registration'
and date_entered > '2011-02-01'
and date_entered < GetDate()
UNION all
select sum(amount) as "2010" from transactions
where transaction_type = 'registration'
and date_entered > '2010-02-01'
and date_entered < DateAdd(yy, -1, GetDate())

I've also read here on Stack Overflow that PIVOT might be an option but I've yet to see an example that I could manipulate/tweak for the queries above.

Any suggestions for how I can get this data side-by-side? I'm sure I'm overlooking something simple.

Many thanks!

回答1:

You want a "pivot", which is essentially a calculation of the form sum(test * amount).
Here's how to do a pivot in your case:

select
    sum(case when date_entered between '2011-02-01' and < GetDate() then amount else 0 end) as "2011",
    sum(case when date_entered between '2010-02-01' and DateAdd(yy, -1, GetDate() then amount else 0 end) as "2010"
from transactions
where transaction_type = 'registration';

回答2:

The quick and UGLY solution is this:

SELECT (
select sum(amount) as "2011" from transactions
where transaction_type = 'registration'
and date_entered > '2011-02-01'
and date_entered < GetDate() ) as '2011',
(select sum(amount) as "2010" from transactions
where transaction_type = 'registration'
and date_entered > '2010-02-01'
and date_entered < DateAdd(yy, -1, GetDate())) as '2010'

You can use this for a one-off query but is certainly something I want add to a production system.

For a good example on PIVOT check this one: http://rajaramtechtalk.wordpress.com/2008/05/13/how-to-use-pivot-in-sql-2005/

Your problem is that you are starting from February, so using DATEPART year wont work for you and you may have to use month and them do some work with the results.