Tags: avg, divided, incorrect, keyword, number, price, programming, return, rows, sql, statement, sum, syntax, table, titles, writing

incorrect syntax near the keyword from

On Programmer » SQL

3,236 words with 3 Comments; publish: Thu, 29 May 2008 05:04:00 GMT; (20078.13, « »)

I am writing a SQL statement to return sum of price divided by number of

rows in titles table. I know this is exactly the same as AVG(price) but I

just try to implement myself.

However, the following SQL statement yields error: "incorrect syntax near

the keyword 'FROM'" and no idea why.

SELECT (SUM(price)

FROM titles) /

(SELECT COUNT(*)

FROM titles))

FROM TITLES

any ideas'

All Comments

Leave a comment...

  • 3 Comments
    • SELECT (SELECT SUM(price)FROM Titles)

      / (SELECT COUNT(*) FROM Titles)

      FROM Titles;

      This is not the same as

      SELECT AVG(price)FROM Titles;

      You will get the quotient repeated for each line in Titles in your weird

      query. You will also get rows with NULLs dropped from SUM(price) but

      they will be counted in the COUNT(*).

      --CELKO--

      =========================== Please post DDL, so that people do not have to guess what the keys,

      constraints, Declarative Referential Integrity, datatypes, etc. in your

      schema are.

      *** Sent via Developersdex http://www.developersdex.com ***

      Don't just participate in USENET...get rewarded for it!

      #1; Thu, 29 May 2008 05:06:00 GMT
    • Matt,

      > I am writing a SQL statement to return sum of price divided by

      > number of rows in titles table. I know this is exactly the same

      > as AVG(price) but I just try to implement myself.

      > However, the following SQL statement yields error: "incorrect

      > syntax near the keyword 'FROM'" and no idea why.

      > SELECT (SUM(price)

      > FROM titles) /

      > (SELECT COUNT(*)

      > FROM titles))

      > FROM TITLES

      Try this:

      select sum(price) / count(*)

      from titles

      or this to eliminate titles that do not have a price:

      select sum(price) / count(*)

      from titles

      where price is not null

      Linda

      #2; Thu, 29 May 2008 05:07:00 GMT
    • Matt

      When I ran this, I also got a message about error near ")"

      If you count your parens, there are an odd number, so you know that has to

      be wrong.

      But you are making this much too complicated.

      Why not:

      SELECT SUM(price)/ COUNT(*)

      FROM TITLES

      But if you want to do it with sub-selects, each aggregate must be a complete

      stand alone query, and then you need a SELECT for the whole thing:

      SELECT

      ( SELECT SUM(price)

      FROM titles) /

      (SELECT COUNT(*)

      FROM titles)

      FROM TITLES

      But that is probably not what you want, because it will perform the division

      of the aggregates for every row in the table.

      --

      HTH

      --

      Kalen Delaney

      SQL Server MVP

      www.SolidQualityLearning.com

      "Matt" <mattloude.sql.todaysummary.com.hotmail.com> wrote in message

      news:#IwBnjH2DHA.2572.sql.todaysummary.com.TK2MSFTNGP12.phx.gbl...

      > I am writing a SQL statement to return sum of price divided by number of

      > rows in titles table. I know this is exactly the same as AVG(price) but I

      > just try to implement myself.

      > However, the following SQL statement yields error: "incorrect syntax near

      > the keyword 'FROM'" and no idea why.

      > SELECT (SUM(price)

      > FROM titles) /

      > (SELECT COUNT(*)

      > FROM titles))

      > FROM TITLES

      >

      > any ideas'

      >

      #3; Thu, 29 May 2008 05:08:00 GMT