0

Please help I don't know what I'm doing wrong.

select state, count(CID)
from customers
group by CID asc;

I'm trying to get the states in ascending order based on the number of unique CID's associated with it. I keep getting this error:

ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause: *Action: Error at Line: 2 Column: 3

ekad
  • 14,436
  • 26
  • 44
  • 46
user2991463
  • 27
  • 1
  • 3

2 Answers2

2

If you want to order your resultset, you need to use the order by clause, as shown below.

Also, you want to group by state rathern than by CID.

select 
  state, 
  count(distinct CID) 
from 
  customers  
group by 
  state
order by 
  count(distinct CID) asc;

Since your question mentiones unique CID's associated (of which I am not 100% sure how this is to be interpreted), you might want to consider using count(distinct CID) instead of count(CID).

René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
2

You've mixed ORDER BY and GROUP BY statements. The correct query should be something like this:

select state, count(CID)
from customers
group by state
ORDER BY 2 asc

And in order to get the count of unique CID, you need to add DISTINCT statement in COUNT function:

select state, count(DISTINCT CID)
from customers
group by state
ORDER BY 2 asc
Mikhail
  • 1,540
  • 2
  • 13
  • 13