count by id problem - 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)

count by id problem

Author

Topic

mrsaif
Starting Member

43 Posts

Posted - 2006-04-27 : 04:01:10

I have a table "tbl_job_emails"
job_id Job_date
1 12/4/2005
2 12/5/2005
.
another table "tbl_send_emails"
email_id, job_id, email_status_id
1 1 1
2 1 1
3 1 2
4 1 2
5 2 1
6 2 2
--------------------------------
I want to count jobs by email_status_id(1 of sent 2 for not sent)
I want this result by using on Query
job_id sent not_sent
1 2 2
2 1 2
------------------------------------
How should it do this in one query.
regard
Saif

Muhammad Saifullah

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-27 : 04:04:34

Select job_id, Sum(case when email_status_id=1 then 1 else 0 end) as sent,
Sum(case when email_status_id=2 then 1 else 0 end) as not_sent from yourTable group by job_id

Madhivanan

Failing to plan is Planning to fail

mrsaif
Starting Member

43 Posts

Posted - 2006-04-27 : 04:14:30

Thanks madhivanan It Realy Worked for me. :)

Muhammad Saifullah

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-27 : 05:10:28

Well. Read about Cross-tab Reports in sql server help file for more information

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources