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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 select distinct and group by
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

bv_avq
Starting Member

2 Posts

Posted - 08/23/2006 :  10:11:10  Show Profile
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
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 08/23/2006 :  10:38:03  Show Profile
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

Edited by - Srinika on 08/23/2006 10:39:31
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/23/2006 :  11:38:48  Show Profile  Visit nr's Homepage
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.

Edited by - nr on 08/23/2006 11:39:08
Go to Top of Page

bv_avq
Starting Member

2 Posts

Posted - 08/24/2006 :  10:06:00  Show Profile
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000