CREATE TABLE customer (
lname VARCHAR (30),
fname VARCHAR (30) NOT NULL,
street VARCHAR (30) NOT NULL,
city VARCHAR (30) NOT NULL,
zipcode NUMERIC (5) NOT NULL,
state VARCHAR (2) NOT NULL,
phone VARCHAR (12) NOT NULL,
credit_org VARCHAR (30),
CONSTRAINT customer_pk PRIMARY KEY (id)
SELECT * FROM (
when CREDITSCORE between 500 and 600 then 'LOWER RANGE(500-600)'
when CREDITSCORE <= 700 then 'AVERAGE RANGE(600-700)'
else 'PREMIUM RANGE(700+)'
end as CREDITSCORE_RANGE,
count(state) FOR state IN ('PA', 'CA', 'NY', 'MD')
I don't think your ranges are quite right for the query you've written, though the wording of the assignment is ambiguous as 'between' is inclusive - so as the question is worded, a credit score of exactly 600 would appear in both the 'lower' and 'average' brackets. Your version will put 600 in the 'lower' bracket, but it's debatable which it should be in; I'd think it should be 'average' from the other definitions, but it's unclear. There is no bracket in the question for scores less than 500, but if you have any of those then your current code will include them in the 'average' bracket, since they are less than 700 but not between 500 and 600.
So I'd have interpreted that as:
SELECT * FROM ( SELECT case when CREDITSCORE >= 500 and CREDITSCORE < 600 then 'LOWER RANGE(500-600)' when CREDITSCORE >= 600 and CREDITSCORE < 700 then 'AVERAGE RANGE(600-700)' when CREDITSCORE >= 700 then 'PREMIUM RANGE(700+)' end as CREDITSCORE_RANGE, state FROM customer ) PIVOT ( count(state) FOR state IN ('PA' as pa, 'CA' as ca, 'NY' as ny, 'MD' as md) );
Your question title refers to ROLLUP, and to get the total row you can use that function:
SELECT creditscore_range, sum(pa) AS pa, sum(ca) AS ca, sum(ny) AS ny, sum(md) AS md FROM ( SELECT * FROM ( SELECT CASE WHEN creditscore >= 500 AND creditscore < 600 THEN 'LOWER RANGE(500-600)' WHEN creditscore >= 600 AND creditscore < 700 THEN 'AVERAGE RANGE(600-700)' WHEN creditscore >= 700 THEN 'PREMIUM RANGE(700+)' END AS creditscore_range, state FROM customer ) PIVOT ( COUNT(state) FOR state IN ('PA' AS pa, 'CA' AS ca, 'NY' AS ny, 'MD' AS md) ) ) GROUP BY ROLLUP (creditscore_range);
If you do have any scores below 500 then Both will include a line for those with the
creditscore_range as null; which is confusing with the
ROLLUP version. You may want to filter any scores less than 500 out from the innermost query, but again it isn't clear if that is necessary or desirable.
I'm not sure that the assignment is looking for when it talks about ranking though. That implies changing the column order based on the values they contain. Ranking by state would make more sense if the data was pivoted the other way.