Solved

MS SQL 2008 Select

Posted on 2015-01-11
5
72 Views
Last Modified: 2015-05-12
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
Comment
Question by:bounty457
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 40543189
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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40543493

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
 
LVL 11

Expert Comment

by:Goodangel Matope
ID: 40543707
You need to group by all fields not being summed

so your group by clause should include payment date
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40543901
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
 
LVL 24

Expert Comment

by:mankowitz
ID: 40548162

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question