help modifying 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

Transact-SQL (2000)

help modifying query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-02-20 : 22:47:37

Hi,

I did up a query awhile ago that helps me determine what email provider is being used by members that have registered in the database. I need to extend a little functionality to this and I'm unsure how to go about it.

This is the format the example data is being brought back now

hotmail.com 117141
yahoo.com 80708
aol.com 27554
msn.com 6911
gmail.com 6346

In the table "userDetails" I have a column named "accountStatus" that can be a small range of numbers denoting the status of account. For each email provider I also want to find out the breakdown of each account.

For example status "1" = activated, status "2" = deleted

Please note that I don't necessarily need this part dynamic. I don't mind hand coding each possible "accountStatus"

I need this SPROC to return something like for example

domain domainCount 1 2 3 4

hotmail.com 117141 80343 20342 10111 6345

Can anyone offer me any assistance on this one? If any DML, DDL is needed just let me know and I'll post.

Thanks very much once again!
mike123

CREATE PROCEDURE dbo.select_emailProviders

AS

SET NOCOUNT ON

select domain, count(domain) as domainCount
from
(
select substring(emailaddress, charindex('@',emailaddress,1)+1,len(emailaddress)) domain
from userdetails where active = 1
) d

group by domain
order by domainCount desc

GO

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-02-20 : 23:04:09

A couple of SUMs will do that, one for each status.

Something like :


select 
	domain,
	SUM ( CASE active WHEN 1 THEN 1 ELSE 0 END) as Status1,
	SUM ( CASE active WHEN 2 THEN 1 ELSE 0 END) as Status2,
	SUM ( CASE active WHEN 3 THEN 1 ELSE 0 END) as Status3,
	SUM ( CASE active WHEN 4 THEN 1 ELSE 0 END) as Status4
from
(
select 
	substring(emailaddress, charindex('@',emailaddress,1)+1,len(emailaddress)) domain,
	active
	from userdetails 
) d 

group by domain

Does that work for you ?

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-02-20 : 23:41:10

hey damian!

great to hear from you and thank you very much, that works perfectly! :)

one more question if you don't mind ..

How would I get a column showing the percentage of

(domainCount / status1) as status1Percentage

Thanks again !! hope to talk with u again sometime soon

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-02-20 : 23:53:10

Glad it worked

You should be able to just add :

SUM ( CASE active WHEN 1 THEN 1 ELSE 0 END) / Count(*) as statusPercentage

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-02-21 : 01:05:10

hey damian,

I'm not completely getting you on that last update. Is something missing from your statement?

I'm getting a value of 0 on everything .. did I make a mistake on integration?

Thanks very much!
mike123

select domain, count(domain) as domainCount,

SUM ( CASE active WHEN 1 THEN 1 ELSE 0 END) as 'Active',
SUM ( CASE active WHEN 9 THEN 1 ELSE 0 END) as 'Awaiting Email Validation',

SUM ( CASE active WHEN 1 THEN 1 ELSE 0 END) / Count(*) as statusPercentage1,
SUM ( CASE active WHEN 9 THEN 1 ELSE 0 END) / Count(*) as statusPercentage9

from

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-02-21 : 01:19:14

Sorry Mike, had a brain fart there

It's dividing integers, so everything gets rounded to zero. You'll need to cast it to something else first. Also, I forgot to make it a percentage by multiplying by 100.

( CASE ( SUM ( CASE active WHEN 1 THEN 1 ELSE 0 END) as numeric(6,2)) / Count(*) ) * 100 as statusPercentage1

It might be nicer to do the formatting in your aspx though.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-02-21 : 15:52:04

thanks once again damian!

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources