select distinct and group by - 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

Old Forums

CLOSED - General SQL Server

select distinct and group by

Author

Topic

bv_avq
Starting Member

2 Posts

Posted - 2006-08-23 : 10:11:10

Hi,

i have 2 tables email and customers_type

fist table : email : id/email/id_customers_type

second table : customers_type : id/type

example :
Fields of email :
1 - xxx@yahoo.fr - 10
2 - xx1@hotmail.com - 20
3 - yy@gmail.com - 2
...

Fields of customers_type :
1 - purchase_product_xx
2 - evalaution_product_yy
10 - purchase_product_zz
20 - purchase_product_ww
...

i would like to know how many emails are contained in different customers types.
For example, if the "xxx@yahoo.fr" is contained in "10 - purchase_product_zz" and "20 - purchase_product_ww", I would like to have a count at 2.

In addition, and that is where our issue occured, if the same email appears several times in the same customer type, I would like to make it count as only 1. The thing is that we had problem using the "distinct" feature in the select query.

here is our query :

select email,count(email) as nb_email from email
where id in (select id from customers_type where libelle like '%purchase%')
group by email
having count(email) > 2
order by count(email) desc

but this query with the distinct don't work :

select email,count(distinct email) as nb_email from email
where id in (select id from customers_type where libelle like '%purchase%')
group by email
having count(email) > 2
order by count(email) desc

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-23 : 10:38:03

Try this

create table #email ([id] int, email varchar(100), id_customers_type int)
insert into #email values(1, 'xxx@yahoo.fr' ,10)
insert into #email values(2, 'xx1@hotmail.com' ,20)
insert into #email values(3, 'yy@gmail.com' ,2)
-- the following is added for ur eg.
insert into #email values(4, 'xxx@yahoo.fr' ,20)


create table #customers_type ([id] int, [type] Varchar(100))
insert into #customers_type values(1,'purchase_product_xx')
insert into #customers_type values(2,'evalaution_product_yy')
insert into #customers_type values(10,'purchase_product_zz')
insert into #customers_type values(20,'purchase_product_ww')


Select id_customers_type,[type],  count(*) as [# of Records] from #email e
inner join #customers_type c on e.id_customers_type = c.[ID]
group by id_customers_type,[type]


drop table #email
drop table #customers_type

Srinika

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-23 : 11:38:48

select email, count(distinct id_customers_type)
from email
group by email

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bv_avq
Starting Member

2 Posts

Posted - 2006-08-24 : 10:06:00

I found a best way to do it :

Select email, count(distinct e.id) as [# of Records] from #email e
inner join #customers_type c on e.id_customers_type = c.[ID]
and s.type like '%purchase%'
group by email
having count(distinct e.id) > 2
order by count(distinct e.id) desc

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources