Home » SQL & PL/SQL » SQL & PL/SQL » it keep coming up missing expression in line 1
icon1.gif  it keep coming up missing expression in line 1 [message #200091] Fri, 27 October 2006 18:10 Go to next message
TrustGod
Messages: 1
Registered: October 2006
Location: FL
Junior Member
Here is the question: Provide a SQL statement that displays each SHIPSTATE along with the number of orders shipped to each state. Include only those states that more than 3 orders were shipped to.

Here is my answer:
SELECT <shipstate>, <order_to_state>
FROM <tablename>
GROUP BY <state>
HAVING COUNT(<order_to_state>)>3
Re: it keep coming up missing expression in line 1 [message #200095 is a reply to message #200091] Fri, 27 October 2006 18:40 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Poor Butterflytee,
still looking for answers, in all the wrong places.


If you used valid SQL syntax, no errors would be reported.
Angle brackets ("<" & ">") are NOT valid as your are trying (ab)use them.
Valid SQL syntax can be found at http://tahiti.oracle.com in the fine SQL Reference manual.
You just might learn something if you took the time to Read The Fine Manual above.

[Updated on: Fri, 27 October 2006 18:43] by Moderator

Report message to a moderator

Re: it keep coming up missing expression in line 1 [message #200108 is a reply to message #200091] Sat, 28 October 2006 00:06 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi
you should use the following syntax if you want to view the order in detail

select shipstate,order_to_state,count(1)
from tablename
group by shipstate,order_to_state
having(count(1)>=3);

if you want to see only the number of orders to the state then

select shipstate,count(1) number_of_orders
from tablename
group by shipstate
having(count(1)>=3);

i hope it will work

regards

Muhammad Shahid Mughal
Re: it keep coming up missing expression in line 1 [message #200113 is a reply to message #200108] Sat, 28 October 2006 01:27 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Shahidmughal, what is the purpose of COUNT(1)? What makes it different from COUNT(*)?
Re: it keep coming up missing expression in line 1 [message #200115 is a reply to message #200113] Sat, 28 October 2006 02:57 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi
both count rows in a table but the way of counting is different

count(*)
counts all the columns

-------------------------------

count(1)
means the counting rows for first column only

-------------------------------

i usually use count(1) therefore i used count(1) in reply.

and also i have read somewhere in a book that count(1) shows result rapidly than count(*)

but in my opinion there is no difference


regards

Muhammad Shahid Mughal
Re: it keep coming up missing expression in line 1 [message #200116 is a reply to message #200115] Sat, 28 October 2006 03:07 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL> select * from test;

       NO1        NO2
---------- ----------
         1          1
         2          2
         3          3
                    4
                    5

SQL> select count(*),count(1) from test;

  COUNT(*)   COUNT(1)
---------- ----------
         5          5

Note : count function not ignore null values.

**********
And count(*) or count(expr) performance wise are some.

SQL> set autotrace on
SQL> select count(*) from two.test;

  COUNT(*)
----------
         5


Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        393  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(1) from two.test;

  COUNT(1)
----------
         5


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        393  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Re: it keep coming up missing expression in line 1 [message #200119 is a reply to message #200116] Sat, 28 October 2006 04:32 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1156159920245. the challenge is still open. good luck.
Previous Topic: How can i covert/ generate a file(txt,xls,doc) from table data in oracle
Next Topic: how to avoid repetitive data from multiples data
Goto Forum:
  


Current Time: Thu Dec 08 04:22:11 CST 2016

Total time taken to generate the page: 0.09896 seconds