Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

MS SQL 2008 Select

Hello Everybode,

i have the following MS SQL Statement and it works fine:

Select ID , SUM(costs) AS costs1 ,

Sum ( Case when (type = 1 or type = 4) then Amount else 0 End) as costs2

from costs

Group by ID

Open in new window


Now i want to use this:

Select ID , SUM(costs) AS costs1 ,Date_of_Payment AS MyDate

Sum ( Case when (type = 1 or type = 4 AND Year (Date_of_Payment) = 2013 then Amount else 0 End) as costs2

from costs

Group by ID

Open in new window


I get a error Maessage. False Syntay in the near from '='.

Do you have any Ideas?

Thank you very much, Kind regards
Bounty
0
bounty457
Asked:
bounty457
1 Solution
 
mankowitzCommented:
missing parenthesiss?

Select ID , SUM(costs) AS costs1 ,Date_of_Payment AS MyDate,
Sum Case when (type = 1 or type = 4 AND Year (Date_of_Payment) = 2013) then Amount else 0 End as costs2
from costs
Group by ID

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:

1.

Missing a right parentheses (comment above), guessing after type=4 but you'll have to verify

2.

The GROUP BY clause is missing Date_of_Payment, as it's in the select and not aggregated.
SELECT
   ID, 
   SUM(costs) AS costs1,
   Date_of_Payment AS MyDate,
  Sum (CASE WHEN (type = 1 or type = 4) AND Year (Date_of_Payment) = 2013 THEN Amount ELSE 0 END) as costs2
FROM costs
GROUP BY ID, Date_of_Payment

Open in new window


< edited to add comma that Vitor spotted >
0
 
Goodangel MatopeSoftware ArchitectCommented:
You need to group by all fields not being summed

so your group by clause should include payment date
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I think no one saw the missing comma before the SUM.
Jim Horn has the more complete solution so I'll take his query and add the missing comma:
SELECT
   ID , 
   SUM(costs) AS costs1 ,
   Date_of_Payment AS MyDate,
  Sum (CASE WHEN (type = 1 or type = 4) AND Year (Date_of_Payment) = 2013 THEN Amount ELSE 0 END) as costs2
FROM costs
GROUP BY ID, Date_of_Payment

Open in new window


NO POINTS PLEASE
0
 
mankowitzCommented:

1.

@vitor, I had the comma.

2.

@Jim,  Love the numbers. How do you know he wants to group by date_of_payment? He may need to aggregate that with Min or something.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now