SQL Server Forums
Profile | Register | 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.06 seconds. Powered By: Snitz Forums 2000