Query SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query SQL

Author

Topic

matkwan
Starting Member

36 Posts

Posted - 2002-07-24 : 01:53:27

Hi, this is my tbl_Contracts structure:

* ID
* Status (Undetermined, Won, Lost)
* Date

so the output of the table is like:

1, U, 1 Jan 2002
2, W, 1 Feb 2002
3, W, 1 Jan 2002
4, L, 4 Feb 2002
5, W, 6 Feb 2002

can I make a query to the table so that it can return the number of Undetermined, Won And Lost contracts for each month ? eg,

Undetermined, Won, Lost, Month
1 , 1 , 0 , Jan
0 , 2 , 1 , Feb

Thanks
Matt

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-07-24 : 03:12:11

select sum(case status when 'U' then 1 else 0 end) as 'Undetermined',
sum(case status when 'W' then 1 else 0 end) as 'Won',
sum(case status when 'L' then 1 else 0 end) as 'Lost',
datename(m,date) as 'Month'
from tbl_Contracts
group by datename(m,date)

Ramesh

matkwan
Starting Member

36 Posts

Posted - 2002-07-24 : 08:49:34

Thanks Ramesh, would your SQL work in Access 97 ?

Sorry I forgot to mention I am working Access 97, not SQL Server.

I get this error message when trying to run the SQL:
Syntax Error (Missing Operator) in Query Expression 'SUM(Case Status When 'U' Then 1 Else 0 End.

Is there any alternative way to get it to work ?

Matt

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-24 : 10:05:41

quote:

Sorry I forgot to mention I am working Access 97, not SQL Server.

...you do realize you posted this question on a forum called 'Transact-SQL', right?.... And there is a forum here for MS Access question....

I think there is an IIF syntax or something in Access....

<O>

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-24 : 12:03:15

in case Page47's answers seems short...and leaves you wondering which part to fix....it's the IFF part!

Access uses IFF, SQL uses CASE....

TimSinnott
Starting Member

48 Posts

Posted - 2002-09-29 : 00:54:35

Ramesh, your example helped me a lot! :D

Thanks.

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources