Count by Interval

[Guest_imported]

I have the following table:
ID Year Quarter1 Quarter2 Quarter3 Quarter4
--------------------------------------------------------
A1 2000 50 80 120 160
A2 2000 120 70 160 180
A3 2000 80 65 190 220
A1 2001 150 80 220 250

ID & Year is the primary key. Quarter1,Quarter2, Quarter3 & Quarter4 indicate how much the person(ID) spent within the quarter. I want to calculate the query result in such:

Q=Quarter
Spending/Year 2000/Q1 2000/Q2 2000/Q3 2000/Q4
----------------------------------------------------
<50 0 0 0 0
50-100 2 3 0 0
101-150 1 0 1 0
151-200 0 0 2 2
>200 0 0 0 1

 

[bperry]

Hi there, your problem sounds quite interesting.
If you happen to be using MS SQL Server, I could look at on Tuesday evening for you, if no one responds sooner.
(I find response time in this forum is often pretty good.)
However, if not MS sql server, then my apologies; I find I am often useless if not MS....
bp

 

[bperry]

Okay, here is a solution for MS SQL Server.
If this doesn't work in your database, perhaps you can adapt it to suit.

First, here is the DDL to create the table I used to test.
CREATE TABLE Spending (
PersonId char (2) NOT NULL,
SpendYr char (4) NOT NULL,
Q1 int NOT NULL,
Q2 int NOT NULL,
Q3 int NOT NULL,
Q4 int NOT NULL,
Primary Key (PersonId,SpendYr)
)


And here is my query:

Select
Spending,
Sum(Case Quarter when 1 then 1 else 0 END) as Q1,
Sum(Case Quarter when 2 then 1 else 0 END) as Q2,
Sum(Case Quarter when 3 then 1 else 0 END) as Q3,
Sum(Case Quarter when 4 then 1 else 0 END) as Q4
FROM
(
SELECT
(CASE
when Amt < 50 then '(1) <50'
when Amt >=50 and Amt <=100 then '(2) 50-100'
when Amt >= 101 and Amt <= 150 then '(3) 101-150'
when Amt >= 151 and Amt <= 200 then '(4) 151-200'
else '(5) >200'
END) as Spending,
Quarter,
Amt
FROM
(
select SpendYr, 1 as Quarter, Q1 as Amt
from Spending
UNION ALL
select SpendYr, 2, Q2 as Amt
from Spending
UNION ALL
select SpendYr, 3, Q3 as Amt
from Spending
UNION ALL
select SpendYr, 4, Q4 as Amt
from Spending
) dt2
where SpendYr = 2000
) dt1
Group By Spending
Order By Spending
---------------------------
And my output looked like this. Seems to be what you want.

Spending Q1 Q2 Q3 Q4
----------- -- -- -- --
(2) 50-100 2 3 0 0
(3) 101-150 1 0 1 0
(4) 151-200 0 0 2 2
(5) >200 0 0 0 1

(4 row(s) affected)

---------------------------
One final note: in the test data that you provided, there were no quarters that fell into the first bucket ( < 50).
So therefore it doesn't show in this output result.
If your actual data has values that will be in this range, then the '<50' row will show okay.

If your data does not have any '< 50' values, but you still want that row to show up in the output (as all zeros), then I can post another solution that will do that. (Although it wouln't be as efficient as this one.)

Also, you'll notice that I added (1), (2), etc to the front of the descriptions. This was so they would sort in the correct order.

rgrds, etc
brian perry