Reputation: 1210
Not giving detailed information about the table structure and the data:
What might be the reason, that this group by:
select c.state, case
when age <20 then 'u20'
when (age >=20 and age<30 ) then 'o20'
when (age >=30 and age<40 ) then 'o30'
when (age >=40 and age<50 ) then 'o40'
else 'min50'
end age_group,
count(*) amount_trans, sum(t.SELLING_PRICE) sum_price from customers c, transactions t
where t.CUSTOMER_ID=c.CUSTOMER_NO
group by age, state
order by state, age
is returning multiple entries for AGE_GROUP, for example:
STATE AGE_GROUP AMOUNT_TRANS SUM_PRICE
Arizona o30 26 667609
Arizona o30 84 913807
Arizona o30 34 161111
Arizona min50 2 93791
Arizona min50 3 907
California u20 1 83048
California u20 1 83048
California o20 1 54772
May aim is:
STATE AGE_GROUP AMOUNT_TRANS SUM_PRICE
Arizona o30 144 1742527
Arizona min50 5 94698
California u20 3 220868
Are there maybe duplicate rows?
Upvotes: 1
Views: 3971
Reputation: 146349
If you want to group by AGE_GROUP you need to, er, group by AGE_GROUP.
select state,
age_group,
count(*) amount_trans,
sum(t.SELLING_PRICE) sum_price
from (
select c.state,
case
when age <20 then 'u20'
when (age >=20 and age<30 ) then 'o20'
when (age >=30 and age<40 ) then 'o30'
when (age >=40 and age<50 ) then 'o40'
else 'min50'
end age_group,
t.SELLING_PRICE
from customers c,
transactions t
where t.CUSTOMER_ID=c.CUSTOMER_NO
)
group by age_group, state
order by state, age_group
Upvotes: 2
Reputation: 13248
Change to:
select c.state,
case
when age <20 then 'u20'
when (age >=20 and age<30 ) then 'o20'
when (age >=30 and age<40 ) then 'o30'
when (age >=40 and age<50 ) then 'o40'
else 'min50'
end age_group,
count(*) amount_trans,
sum(t.SELLING_PRICE) sum_price
from customers c
join transactions t
on t.CUSTOMER_ID = c.CUSTOMER_NO
group by c.state,
case
when age <20 then 'u20'
when (age >=20 and age<30 ) then 'o20'
when (age >=30 and age<40 ) then 'o30'
when (age >=40 and age<50 ) then 'o40'
else 'min50'
end
order by state,
age_group
(group by the case statement, not age)
On a side note, you can use the column alias in the ORDER BY but not the GROUP BY clause. You have to copy and paste the full case statement into the GROUP BY.
Upvotes: 1
Reputation: 1308
Its because you are grouping by age, state. Did you mean group by age_group, state?
Upvotes: 1