Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Sum with no group by

    I want to run a much larget SQL statement, but for examples sake this is a good starting point
    Code:
    Select efName, elName, eAddress, SUM(Convert(money, bonus1)+Convert(money, bonus2)+Convert(money, bonus3)) As TotalBonus, ePay FROM tableEInfo
    It is telling me that I have to use Group By, but the problem is that most of my fields are text fields, which it looks like have to be converted in order to use with a group by statement. Is it possible to use the sum function with no group by statemnet?

  2. #2
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Sum with no group by

    You could make SUM a window function...

    Code:
    SELECT
      efName
     ,elName
     ,eAddress
     ,SUM(CONVERT(money,bonus1)+CONVERT(money,bonus2)+CONVERT(money,bonus3))
      OVER (PARTITION BY eID) -- eID being the identifier of tableEInfo
      as TotalBonus
     ,ePay
    FROM tableEInfo
    ...thus, eliminating the GROUP-BY from the WHERE clause.

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    I have the above SQL pasted into my SQL Server, and it keeps telling me Incorrect syntax near the keyword over. employeeID is the AutoNumber (Good gues by the way) and PK set up for the table so I do not know why I am getting this:
    Msg 156, Level15, State1
    Incorrect Syntax near the keyword 'Over'

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by jo15765 View Post
    Msg 156, Level15, State1
    Incorrect Syntax near the keyword 'Over'
    Your are probably using an outdated (if not unsupported) SQL Server version
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    I am running SQL Server 2008 Management Studio...is this not applicable for this version?

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by jo15765 View Post
    I am running SQL Server 2008 Management Studio
    That's just your SQL tool. What is important is the version of your SQL Server
    You can check by running
    Code:
    select @@version
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #7
    Join Date
    Feb 2012
    Posts
    188
    Its SQL SERVER 2000

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by jo15765 View Post
    Its SQL SERVER 2000
    As I said: (completely) outdated
    (and no longer supported if I'm not mistaken)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  9. #9
    Join Date
    Feb 2012
    Posts
    188
    Lovely! Well thanks for the help anyway!

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Unfortunately, twelve years is a long time in the computer industry.

    While TEXT columns can store just about anything you can represent as text, they are really rough for manipulating what gets stored into them because the TEXT datatype has no insight into what the stored data means. The intent of TEXT columns was basically a "last ditch effort" to store information without resorting to brute force binary.

    What Shammat tried to do was to work around that limitation using a relatively new SQL feature that didn't exist when SQL 2000 was being written and actively supported. Unfortunately, that won't work on SQL 2000 any better than EFI (Electronic Fuel Injection) would work on a Model-T.

    Transact-SQL does in fact allow the use of Sum() without a GROUP BY clause, but the effect would not be what you want. Doing that will create a Sum for any rows that pass the WHERE clause (in this case the whole table), giving you just a single lump sum instead of a sum for each group that interests you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Pat Phelan View Post
    What Shammat tried to do
    I don't deserve the credit. It was homerow who posted the solution.

    using a relatively new SQL feature that didn't exist when SQL 2000
    Well it was a new features to SQL Server. Other DBMS have had that for ages (Oracle, DB2 come to mind)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Could it be that its running under a later version, but in a lower compatibility level?
    Try running this and see what compatibility level your database is in:

    select name, Compatibility_Level from sys.databases order by name
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by shammat View Post
    I don't deserve the credit. It was homerow who posted the solution.

    Well it was a new features to SQL Server. Other DBMS have had that for ages (Oracle, DB2 come to mind)
    ISO only approved the CTE support as part of SQL-99, which wasn't formally released until months after MS-SQL 2000 had shipped... I don't think that DB2 version 6 had CTE support, and I don't think that Oracle Version 8 had CTE support or even CONNECT BY in 2000 (although I think it was released in a later product update).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Pat Phelan View Post
    and I don't think that Oracle Version 8 had CTE support or even CONNECT BY in 2000
    Yes it did. CONNECT BY was available at least in 7.3 maybe even earlier. Although recursive CTEs only appeared in the latest 11.2 release (regular i.e. non-recursive CTEs were available since 9.0).

    Analytical functions (Oracle's name for windowing functions) were available since 8.1 (aka 8i), I think that was released '95 or '96
    Last edited by shammat; 10-26-12 at 19:05.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •