SQL nested query - 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

SQL Server Development (2000)

SQL nested query

Author

Topic

newber
Starting Member

1 Post

Posted - 2007-07-06 : 10:15:52

have a table with the following colums

id, domain, status

domain contains different major domains like aol.com,yahoo.com ect ect

status has sent, bounce, defer

I want to do a count for each of these per domain, in a nested query

So it would look like this

Domain | Sent | Bounce | Defer
aol.com | 3000 | 34 | 34

Any idea on how I would order my nested queries?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-06 : 10:21:57

[code]SELECT Domain,
[Sent] = COUNT(CASE WHEN status = 'Sent' THEN 1 END),
[Bounce] = COUNT(CASE WHEN status = 'Bounce' THEN 1 END),
[Defer] = COUNT(CASE WHEN status = 'Defer' THEN 1 END)
FROM yourtable
GROUP BY Domain
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-06 : 23:31:39

Also read about Cross-tab reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-06 : 23:32:44

quote:

Originally posted by khtan

SELECT Domain,
       [Sent]   = COUNT(CASE WHEN status = 'Sent'   THEN 1 END),
       [Bounce] = COUNT(CASE WHEN status = 'Bounce' THEN 1 END),
       [Defer]  = COUNT(CASE WHEN status = 'Defer'  THEN 1 END)
FROM   yourtable
GROUP BY Domain

KH
[spoiler]Time is always against us[/spoiler]

Do you have any tool that colours your code?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-07 : 02:57:03

quote:
Do you have any tool that colours your code?

It is a macro in my editor courtesy of Ifor. See this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85262

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-07 : 04:01:10

quote:
Originally posted by khtan

quote:
Do you have any tool that colours your code?

It is a macro in my editor courtesy of Ifor. See this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85262

KH
[spoiler]Time is always against us[/spoiler]

That seems cool
I think the same code wont work in EXCEL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources