CODEWITHSUNDEEP
sqloracle-databasegroup-by
royskatt
royskatt

Reputation: 1210

SQL - Group by not grouping as expected

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

Answers (3)

APC
APC

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

Brian DeMilia
Brian DeMilia

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

gmarintes
gmarintes

Reputation: 1308

Its because you are grouping by age, state. Did you mean group by age_group, state?

Upvotes: 1

Related Questions