CODEWITHSUNDEEP
sqloracle-database
flower
flower

Reputation: 2242

What's wrong with my sql with ORA-00904?

What's wrong with my sql?I work's well in sybase but work wrong in oracle.

    select Col1 as bus_type,
           count(*) as cnt,
           sum(count(*)) as sums  
      from t_form_25
     where Node_value ='6' 
  group by bus_type
  order by cnt desc

And it shows:

[Err] ORA-00904: "BUS_TYPE": invalid identifier

How to fix it? It works well in sybase.

Upvotes: 1

Views: 169

Answers (3)

SMS
SMS

Reputation: 84

select Col1 as bus_type,
count(*) as cnt,
 from  t_form_25
where Node_value ='6' 
group by Col1
order by cnt desc;

Upvotes: 0

sagi
sagi

Reputation: 40481

You have two errors in your code!

  • You cannot refer to an alias on the GROUP BY clause on the same level he was created.

  • You can't do an aggregation function on aggregation function on the same level! again, you have to wrap it with another select if you want to use it.

So:

SELECT bus_type,cnt,sum(cnt) OVER(ORDER BY 1) sum_cnt
FROM (
    select Col1 as bus_type,
           count(*) as cnt  
      from t_form_25
     where Node_value ='6' 
  group by Col1)
GROUP BY bus_type,cnt
order by cnt desc

I don't entirely understand what did you try to do here, maybe you will have to omit one of the columns because right now the sum will always be equals to the cnt.

Upvotes: 4

cableload
cableload

Reputation: 4385

You cannot group by the alias you have..Just try this..

    select Col1 as bus_type,
           count(*) as cnt,
           sum(count(*)) as sums  
      from t_form_25
     where Node_value ='6' 
  group by Col1
  order by cnt desc

Upvotes: 1

Related Questions