Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I'm working in oracle and I can't figure out what's wrong with this statement. The error it gives me is must name this expression with a column alias

CREATE TABLE shipping_costs_grouped AS
    SELECT SUM(invoice_amt), SUM(entered_weight), SUM(billed_weight), SUM(net_amt), MAX(ZONE), pps_num
    FROM shipping_costs GROUP BY pps_num;

I know it has something to do with the group by statement but I don't know how to resolve it

share|improve this question

1 Answer

up vote 6 down vote

You need to add aliases for the computed columns so that Oracle knows what names the columns of the table should have

CREATE TABLE shipping_costs_grouped 
AS
    SELECT SUM(invoice_amt) total_invoice_amt, 
           SUM(entered_weight) total_entered_weight, 
           SUM(billed_weight) total_billed_weight, 
           SUM(net_amt) total_net_amt, 
           MAX(ZONE) max_zone, 
           pps_num
      FROM shipping_costs 
     GROUP BY pps_num;

should be valid syntax.

Whether it is actually appropriate to create a table for this sort of thing is a question you'd have to answer. My bias would be that it would be more appropriate to create a view or potentially a materialized view but I don't know the exact nature of the problem you're trying to solve.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.